How to calculate Percentage out of Total value in DAX (Power BI Desktop)

Hell-1931 picture Hell-1931 · Aug 8, 2019 · Viewed 18.8k times · Source

I have the following Slicer in Power BI Desktop, where # of Clients is calculated as Count(Distinct(Fact.EHRTransaction.ClientFK)) in my data model:

enter image description here

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 -

enter image description here

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])

Answer

janvanwerkhoven picture janvanwerkhoven · Aug 8, 2019

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.