Forum Discussion
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!
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.
4 Replies
Could you provide a few rows of sample/dummy data?
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!
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.
