Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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])
like image 688
Hell-1931 Avatar asked Mar 04 '23 14:03

Hell-1931


1 Answers

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.

like image 109
janvanwerkhoven Avatar answered Apr 30 '23 23:04

janvanwerkhoven