Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Before you spend any more effort on this - read up on the new dynamic measure formatting feature in Power BI April 2023.
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
User | Count |
---|---|
97 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |