Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |