Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DataInsights
Super User
Super User

Calculate group total based on field not in fact table

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!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




1 ACCEPTED SOLUTION
d_gosbell
Super User
Super User

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]) )
)

View solution in original post

3 REPLIES 3
d_gosbell
Super User
Super User

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!





Did I answer your question? Mark my post as a solution!

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.