Using 3 (or more) calculation groups on a visual

[dedelman_clng]

Hi. I have recently started using Calculation Groups to streamline financial reports in Power BI. I have one calculation group that is for the Time Period

Current Month (Date = previous month/year)

YTD (TotalYTD, Date < today's month/year)

Total YTD (TotalYTD for all year)

My 2nd calculation group is the for the "Value Type", which is essentially this pattern

Actual (Sum from Actuals table, blank if Time Period = "Total Year")

Budget (Sum from Budget table, blank if Time Period = "Current Month")

Forecast (Sum from Forecast table, blank if Time Period = "YTD")

Variance (subtracting one of the above 3 from another, depending on Time Period)

Variance % (Variance divided by one of the above 3, depending on Time Period) 

I am successful in using the two calculation groups together. However, on different pages of the report, the measures for Actual/Budget/Forecast are different enough that the differences cannot be handled by slicers or filters. 

So far, I have created 5 different calculation groups, each having the exact same pattern except for which measure is being used in the items. What I mean by this is that in all "Value Type" groups, calculation items are always

Active
IF( SELECTEDVALUE( 'Time Period'[Name] ) = "Total Year", BLANK(), IF( [ActMeasure] = 0, BLANK(), [ActMeasure] ) ) / 1000

Budget
IF( SELECTEDVALUE( 'Time Period'[Name] ) = "Curr Month", BLANK(), IF( [BudMeasure] = 0, BLANK(), [BudMeasure] ) ) / 1000

Forecast
IF( SELECTEDVALUE( 'Time Period'[Name] ) = "YTD", BLANK(), IF( [FCMeasure] = 0, BLANK(), [FCMeasure] ) ) / 1000

...just changing out ActMeasure/BudMeasure/FCMeasure each time (the "/1000" is to force the data into thousands for display purposes).

I was trying to see if I could make a 3rd calculation group that would contain the various scenarios that currently have their own "Value Type" calculation group, but I couldn't wrap my head around how a 3-way calculation group would work.

The basic visual I'm looking to do is this

and the idea would be that the 3rd calculation group would exist on the page as a slicer.

Does anyone have experience with using 3 calculation groups on a single visual, or should I just continue cloning the calculation groups for each scenario?

Thanks,

David

[lbendlin]

Before you spend any more effort on this - read up on the new dynamic measure formatting feature in Power BI April 2023.

[dedelman_clng]

Hi @lbendlin . Thank you for the reply. I am aware of the new dynamic measure formatting, but I don't think that is my issue (aside from the /1000 part).

My issue is that I have several categories that I want to "apply"outside of the Time Period/Value Type "matrix". For example, Category A might be to caluculate Actual/Budget/Forecast with a filter on Account, while Category B is to calculate ActualBudget/Forecast with a filter on Cost Group. So currently I am cloning the Value Type calculation group into standalone calculation groups for each of Category A/Category B/etc.

Calc Group "Value Type CatA"
Calculation item "Active"

CALCULATE([MeasureA], FILTER(ALL(Account), Account = "123"))

Calc Group "Value Type CatB"
Calcuation item "Active"

CALCULATE([MeasureA], FILTER(ALL(Cost Group), Cost Group = "ABC"))

Everything works as expected by using cloned calculation groups, but it is a lot to maintain. If there was a way to bring in a 3rd calcuation group I wouldn't have to make a clone each time there was a new category of calculation.

It's entirely possible that the solution might lie in the data model itself, but before I went to revamp the whole thing I wanted to see if anyone had experience with using the intersection of 3 calculation groups at one time.

Thanks,

David