Forum Discussion

dedelman_clng's avatar
dedelman_clng
Community Champion
3 years ago

Using 3 (or more) calculation groups on a visual

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

Need Help

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

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

    • dedelman_clng's avatar
      dedelman_clng
      Community Champion
      3 years ago

      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