Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I'm trying to calculate the ratio "Count of Procedure Code" / "Count of Procedure Code by EM Category". The two tables below are joined on ProcedureCode.
FactProcedure |
|
|
|
|
|
Date | Procedure Code | Provider |
1-Jan | 99211 | Pete |
1-Jan | 99212 | Pete |
2-Jan | 99211 | Pete |
2-Jan | 99211 | Pete |
3-Jan | 99214 | Pete |
3-Jan | 99214 | Pete |
3-Jan | 99215 | Pete |
DimProcedure |
|
|
|
Procedure Code | EM Category |
99211 | New |
99212 | New |
99213 | New |
99214 | Established |
99215 | Established |
Here's the output I'm expecting:
Provider | Procedure Code | EM Category | Count by EM Category | % of EM Category |
Pete | 99211 | New | 4 | 75.00% |
Pete | 99212 | New | 4 | 25.00% |
Pete | 99213 | New | 4 | 0.00% |
Pete | 99214 | Established | 3 | 66.67% |
Pete | 99215 | Established | 3 | 33.33% |
Any help is much appreciated!
Proud to be a Super User!
Solved! Go to Solution.
I think something like the following should get you the ratio you are after except maybe not the 0% against procedure 99213. If reporting on data that is not in the fact table is important you might need to use an IF checking for a 0 rowcount and return an explicit 0.
Ratio = DIVIDE(
COUNT( FactProcedure[ProcedureCode] )
, CALCULATE( COUNT( FactProcedure[ProcedureCode] ) , ALLEXCEPT( 'Dim Procedure'[EM Category]) )
)
I think something like the following should get you the ratio you are after except maybe not the 0% against procedure 99213. If reporting on data that is not in the fact table is important you might need to use an IF checking for a 0 rowcount and return an explicit 0.
Ratio = DIVIDE(
COUNT( FactProcedure[ProcedureCode] )
, CALCULATE( COUNT( FactProcedure[ProcedureCode] ) , ALLEXCEPT( 'Dim Procedure'[EM Category]) )
)
Thanks to both of you!
Proud to be a Super User!
Hi Darren, big respect for your contribution to the community.
May I just correct the typo in the ALLEXCEPT clause (and what about the old + 0 technique to get the zero value in there)
Ratio = DIVIDE(
COUNT(FactProcedure[Procedure Code]) + 0
, CALCULATE( COUNT( FactProcedure[Procedure Code] ) , ALLEXCEPT(DimProcedure, 'DimProcedure'[EM Category]) )
)
To the original poster, you can separate out the denominator as a measure here to get the 'Count by EM category' column you want
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |