cancel
Showing results for
Did you mean:
Highlighted
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

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

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

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

Frequent Visitor

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

Thanks to both of you!

Announcements

#### 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!

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

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

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

Top Solution Authors
Top Kudoed Authors