I have the following Slicer in Power BI Desktop, where # of Clients is calculated as Count(Distinct(Fact.EHRTransaction.ClientFK)) in my data model:
My goal is to calculate Percentage out of Total (13 639) and add it to this slicer as a Measure or another Column, like:
Gender # of Clients Total Clients
Unknown 2 0.00%
Intersex 13 0.00%
Transgender 18 0.00%
Female 662 0.04%
Male 832 0.05%
(Not Recorded) 12 112 72.79%
I tried adding the following Column:
Percentage = 'FactEHRClinicalTransaction'[ClientFK]/
CALCULATE(SUM('FactEHRClinicalTransaction'[ClientFK]),ALLSELECTED())
But I am getting an incorrect values -
Please, help or advice!
Update: Finally, found a solution: In order to achieve these calculations, needed to add a Measures for each operation. And then, use them (not the fields) in final % calculation -
# of Clients = DISTINCTCOUNT('Fact EHRClinicalTransaction'[ClientFK])
# of Clients_Total =
CALCULATE(DISTINCTCOUNT('Fact EHRClinicalTransaction'[ClientFK]),
ALLSELECTED('Fact EHRClinicalTransaction'))
% of Clients = DIVIDE('Fact EHRClinicalTransaction'
[# of Clients],'Fact EHRClinicalTransaction'[# of Clients_Total])
It looks like you are missing an aggregation in the first part of your division and are summing the FKs in the second part instead of counting. Try this:
Percentage =
DIVIDE (
DISTINCTCOUNT ( 'FactEHRClinicalTransaction'[ClientFK] ),
CALCULATE (
DISTINCTCOUNT ( 'FactEHRClinicalTransaction'[ClientFK] ),
ALLSELECTED ()
)
)
Using DIVIDE() makes for a safer division.