Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ppgandhi11
Helper V
Helper V

Dax calculation help for %

Hi,

 

I am not able to get this working. I have a recordset that has following fields:

 

Service - field

ProviderNPI - field

# of Patients seen - measure (count(query[patientkey])

 

if ProviderNPI has seen 100 patients total and out of that only 30 have specific service prescribed, I want to calculate the 30% for it.

 

when I use matrix and select service and ProviderNPI, the calculation becomes very much service specific so the % becomes 1.00

 

% formula i am trying to use is:

 

% for Provider = DISTINCTCOUNT(BaseQuery[ptkey])/CALCULATE(DISTINCTCOUNT(BaseQuery[ptkey]), ALL(BaseQuery[ptkey]))

 

it should be: 30/100 and it is doing 30/30 since the denominator is service specific i think. How can I get this working? Please help!

1 ACCEPTED SOLUTION

I found the solution.

 

below link helped (Thanks @greg_deckler for answering it there)

 

https://community.powerbi.com/t5/Desktop/Make-measure-ignore-specific-filter/td-p/336870

 

I used the denominator value as:

 

# of Total Patients = CALCULATE(DISTINCTCOUNT(BaseQuery[ptkey]), all(BaseQuery[Service]))

 

This ignores the Service filter and gives the desired value! Thanks @Aron_Moore for looking into this.

View solution in original post

4 REPLIES 4
ppgandhi11
Helper V
Helper V

anyone? I still need help. unable to get this done.

 

I need following report:

 

NPI                                          %of patients with prescribed service code

A                                                  23/45 * 100    (23 patients prescribed selected service code by NPI A out of 45 patients seen by NPI A)

 

:

:

 

When I select any service code from the control, it always becomes 23/23. Let me know if you want to know the formula I am using for numerator and denominator. Basically I need to ignore the service selection for denominator and I am not able to get through that.

 

Please help! 

Capture.PNG

sample data provided. There is a control filter called service on this page.

 

My problem is: when all the services are selected, column B and C both shows 45 for NPI A.  When I select any service, both the B and C would change it to 23 (assuming that for selected service 23 patients were prescribed that service).

 

Formula I am trying to use for numerator:

# of Patients = calculate(DISTINCTCOUNT(BaseQuery[ptkey]))

 

for denominator:

not working, I am trying different functions.  Denominator is the problem in my case. So the % always becomes 100.

 

Thanks!

I found the solution.

 

below link helped (Thanks @greg_deckler for answering it there)

 

https://community.powerbi.com/t5/Desktop/Make-measure-ignore-specific-filter/td-p/336870

 

I used the denominator value as:

 

# of Total Patients = CALCULATE(DISTINCTCOUNT(BaseQuery[ptkey]), all(BaseQuery[Service]))

 

This ignores the Service filter and gives the desired value! Thanks @Aron_Moore for looking into this.

Aron_Moore
Solution Specialist
Solution Specialist

Could you provide a few rows of sample/dummy data?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.