Dax calculation help for %

[ppgandhi11]

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!

[ppgandhi11]

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]

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 · 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.

[Aron_Moore]

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