cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ppgandhi11 Member
Member

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

Accepted Solutions
ppgandhi11 Member
Member

Re: Dax calculation help for %

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
Aron_Moore Established Member
Established Member

Re: Dax calculation help for %

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

ppgandhi11 Member
Member

Re: Dax calculation help for %

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! 

ppgandhi11 Member
Member

Re: Dax calculation help for %

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!

ppgandhi11 Member
Member

Re: Dax calculation help for %

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 253 members 2,706 guests
Please welcome our newest community members: