cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
DataInsights
Frequent Visitor

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: Calculate group total based on field not in fact table

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
Super User III
Super User III

Re: Calculate group total based on field not in fact table

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

Super User I
Super User I

Re: Calculate group total based on field not in fact table

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

DataInsights
Frequent Visitor

Re: Calculate group total based on field not in fact table

Thanks to both of you!

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors