Calculate group total based on field not in fact table

[DataInsights]

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!

[d_gosbell · Accepted 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]) )
)

[DataInsights]

Thanks to both of you!

[HotChilli]

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