Calculation Groups: Interaction of rows and columns in a grid

[Andrew-HLP]

Hello.  I am exploring the use of CALCULATION GROUPS.

I have created a group called "Income Statement v2" which has four calculation items...

each of which is linked to a mesure of the same name...

My page has filters for fiscal year and fiscal month which work exactly as expected.

My matrix  uses the calculation group in the rows and three other measures in the columns:

Where:

GL Value = calculate (sum('GL Data'[Value]))

Actual Calc = calculate ([GL Value],'GL Data'[Scenario] = "Actual")

Budget Calc = calculate ([GL Value],'GL Data'[Scenario] = "Budget")

(these measures work fine in other contexts).

I was expecting teh values in the matrix  to reflect the relevant row and column selections but they do not.  The scenario filter is ignored (If I add one to the page then all three columns show whatever is selected on the page).

Where have I gone wrong?

THANK YOU FOR YOUR HELP!

[v-kathullac]

Hi @Andrew-HLP ,

we wanted to kindly follow up to check if the solution is fixed, If it is solved, please mark the helpful reply or share your solution it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,

Chaithanya.

[Andrew-HLP]

Hello everyone - Thanks for your help.   I have been able to move this problem on, but not resolve it.

PROBLEM 1 - CALCULATION GROUPS IN ROWS + MEASURES IN COLUMNS

I can now get this to work provided I write the calculations for the rows "longhand" in the calculation group.  So 'Income' and 'Cost of Sales' work but 'Test Using Measure' doesn't (it returns no data i.e. blank row) .

Visual:

Calculation Group (used in rows):

Code for the calculation group:

Income: CALCULATE( SELECTEDMEASURE() , 'GL Map Acct Level 3'[Acct Level 3 Desc] = "Income")
Cost of Sales: CALCULATE( SELECTEDMEASURE() , 'GL Map Acct Level 3'[Acct Level 3 Desc] = "Cost of Sales")
Test Using Measure: [Income (Test for CG)] 

Where the [Income (Test for CG)] is defined as follows:

Identical to calculation used on income line

Income (Test for CG) = CALCULATE( SELECTEDMEASURE() , 'GL Map Acct Level 3'[Acct Level 3 Desc] = "Income")

I would prefer to use measures in the calculation greoup so that I do not need to define and maintain multiple calculations for Gross Margin, Gross Margin % etc.  

Can measures be used on both axis if they are complementary (i.e. do not use opposing filters etc)?  If so, how?

PROBLEM 2 - CALCULATION GROUPS IN COLUMNS + MEASURES IN ROWS

Similarly, when I use a calculation group in the columns I need to define the formulas "longhand" in teh calculation group- this works fine provided there is a suitable time intelligence function, but if the calculation requires two or more functions to be combined (e.g. 'Actual-1 YTD' and 'Actual-2 YTD') I can't make this work in a calculation group.   

Using Measures (works fine):

Visual:

Code for the measures:

Actual Calc = CALCULATE([GL Value], 'GL Data'[Scenario] = "Actual")
Actual CM = CALCULATE ( [Actual Calc], DATESMTD('Fiscal Calendar'[Date]))
Actual LM = CALCULATE ( [Actual Calc], PREVIOUSMONTH('Fiscal Calendar'[Date]))
Actual NM = CALCULATE ( [Actual Calc], NEXTMONTH('Fiscal Calendar'[Date]))
Actual Qtd = CALCULATE ( [Actual Calc], DATESQTD('Fiscal Calendar'[Date]))
Actual Ytd = CALCULATE ( [Actual Calc], DATESYTD('Fiscal Calendar'[Date], "3/31"))

Actual-1 Calc = CALCULATE ( [Actual Calc], SAMEPERIODLASTYEAR ( 'Fiscal Calendar'[Date] ) )
Actual-1 CM = TOTALMTD([Actual-1 Calc], 'Fiscal Calendar'[Date])
Actual-1 LM = CALCULATE ( [Actual-1 Calc], PREVIOUSMONTH('Fiscal Calendar'[Date]))
Actual-1 NM = CALCULATE ( [Actual-1 Calc], NEXTMONTH('Fiscal Calendar'[Date]))
Actual-1 Qtd = TOTALQTD([Actual-1 Calc], 'Fiscal Calendar'[Date])
Actual-1 Ytd = TOTALYTD([Actual-1 Calc], 'Fiscal Calendar'[Date], "3/31")

Actual-2 Calc = CALCULATE ( [Actual Calc], DATEADD ( 'Fiscal Calendar'[Date], -2, YEAR) )
Actual-2 CM = TOTALMTD([Actual-2 Calc], 'Fiscal Calendar'[Date])
Actual-2 LM = CALCULATE ( [Actual-2 Calc], PREVIOUSMONTH('Fiscal Calendar'[Date]))
Actual-2 NM = CALCULATE ( [Actual-2 Calc], NEXTMONTH('Fiscal Calendar'[Date]))
Actual-2 Qtd = TOTALQTD([Actual-2 Calc], 'Fiscal Calendar'[Date])
Actual-2 Ytd = TOTALYTD([Actual-2 Calc], 'Fiscal Calendar'[Date], "3/31")

With a Calculation Group:

Visual:

Calculation Group (used in columns):

Code for the calculation group:

This Mth: CALCULATE( SELECTEDMEASURE() , DATESMTD('Fiscal Calendar'[Date]) , 'GL Data'[Scenario] = "Actual")
Last Mth: CALCULATE (SELECTEDMEASURE(),PREVIOUSMONTH('Fiscal Calendar'[Date]),'GL Data'[Scenario] = "Actual")
This Mth YTD: CALCULATE(SELECTEDMEASURE(),DATESYTD('Fiscal Calendar'[Date],"3/31"),'GL Data'[Scenario] = "Actual")
Last Yr: CALCULATE (SELECTEDMEASURE(),SAMEPERIODLASTYEAR ('Fiscal Calendar'[Date]),'GL Data'[Scenario] = "Actual")

Last Yr YTD: REQUIRE MEQUIVALENT OF ACTUAL-1 YTD MEASURE (WILL ALSO NEED ACTUAL-2 YTD)

Sample code for the measures:

Income = CALCULATE ([GL Value],'GL Map Acct Level 3'[Acct Level 3 Desc] = "Income") 
Cost of Sales = CALCULATE ([GL Value], 'GL Map Acct Level 3'[Acct Level 3 Desc] = "Cost of Sales" )
Gross Margin (ST) = CALCULATE ([Income] + [Cost of Sales], all('GL Map Acct Level 0') )
Gross Margin % = CALCULATE ( DIVIDE ([Gross Margin (ST)] , [Income] , "-" ), all('GL Map Acct Level 0') )

How can I write formula equivalent to 'Actual-1 YTD' and 'Actual-02 YTD' in a calculation group?

NB: Data shown is random test data

@v-kathullac , @v-vpabbu , @bhanu_gautam 

[Andrew-HLP]

Not a complete solution as such but I have stumbled across the ability to calculate items based on other items in the calculation group (sideways recursion) rather than write out each calculation "longhand" (so for example 'Gross Margin %' calculation is simplified as it can use teh value we have already calculated for 'Gross Margin'.  

Calculation Group:

Code for the Calculation Group:

Income: CALCULATE( SELECTEDMEASURE() , 'GL Map Acct Level 3'[Acct Level 3 Desc] = "Income")

Cost of Sales: CALCULATE( SELECTEDMEASURE() , 'GL Map Acct Level 3'[Acct Level 3 Desc] = "Cost of Sales")

Gross Margin: 
CALCULATE( SELECTEDMEASURE() , 'CG: Report Income Statement V2 TEST'[Income Statement] = "Income" ) +
CALCULATE( SELECTEDMEASURE() , 'CG: Report Income Statement V2 TEST'[Income Statement] = "Cost of Sales" ) 

Gross Margin %:
DIVIDE
(CALCULATE(SELECTEDMEASURE(),'CG: Report Income Statement V2 TEST'[Income Statement] = "Gross Margin" ),
CALCULATE(SELECTEDMEASURE(),'CG: Report Income Statement V2 TEST'[Income Statement] = "Income" ),
"-") 

Update: This doesnt work as well as I hoped.  If I have percentages in the columns (e.g. percentage variances) teh formula in the calculation group adds the percentages (e.g. % Gross Margin Variance is sum of sales variance + cost of sales varaiance rather than calculated based on the values in the Gross Margin line.   

To get around this I changed the expression for gross margin so that instead of adding two differewnt calculate statemenst together I adjusted teh filters to filter on "Income" or "Cost of Sales" but for reasons I cannot determine a second filter doesnt work when referring to other lines in the calculation group so I have had to revert to the fuller version of teh calculations:

Gross Margin: 
CALCULATE( SELECTEDMEASURE() , 
    KEEPFILTERS(
    'GL Map Acct Level 3'[Acct Level 3 Desc] = "Income" || 
    'GL Map Acct Level 3'[Acct Level 3 Desc] = "Cost of Sales"
    ) 
)

Gross Profit: 
CALCULATE( SELECTEDMEASURE() , 
    KEEPFILTERS(
    'GL Map Acct Level 3'[Acct Level 3 Desc] = "Income" || 
    'GL Map Acct Level 3'[Acct Level 3 Desc] = "Cost of Sales"  || 
    'GL Map Acct Level 3'[Acct Level 3 Desc] = "Overheads"     
    ) 
)

Net Profit:
CALCULATE( SELECTEDMEASURE() , 
    KEEPFILTERS(
    'GL Map Acct Level 3'[Acct Level 3 Desc] = "Income" || 
    'GL Map Acct Level 3'[Acct Level 3 Desc] = "Cost of Sales"  || 
    'GL Map Acct Level 3'[Acct Level 3 Desc] = "Overheads" ||     
    'GL Map Acct Level 3'[Acct Level 3 Desc] = "ABP Distributions"
    ) 
)

Retained Profit: 
CALCULATE( SELECTEDMEASURE() , 
    KEEPFILTERS(
    'GL Map Acct Level 3'[Acct Level 3 Desc] = "Income" || 
    'GL Map Acct Level 3'[Acct Level 3 Desc] = "Cost of Sales"  || 
    'GL Map Acct Level 3'[Acct Level 3 Desc] = "Overheads" ||     
    'GL Map Acct Level 3'[Acct Level 3 Desc] = "ABP Distributions" ||  
    'GL Map Acct Level 3'[Acct Level 3 Desc] = "SHP Distributions"
    ) 
)

I can still calculate 'Gross Margin %' etc as before.

More info here: https://learn.microsoft.com/en-us/analysis-services/tabular-models/calculation-groups?view=asallprod... 

[v-kathullac]

Hi @Andrew-HLP ,

Are you still facing the issue if yes  can you share sample pbix file to work around

If it is solved, please mark the helpful reply or share your solution it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,

Chaithanya.

[bhanu_gautam]

@Andrew-HLP When defining calculation items, use the SELECTEDMEASURE function to dynamically reference the measure being modified. For example

CALCULATE(SELECTEDMEASURE(), 'GL Data'[Scenario] = "Actual")

[Andrew-HLP]

Thanks for the speedy response.  Unfortunately that returns the same results...

Formula I used...

Test Actual = CALCULATE(SELECTEDMEASURE(), 'GL Data'[Scenario] = "Actual")

Results:

P.S.  Calculation of [Income] and similar measures looks like this (have tried both options)

Income = CALCULATE ([GL Value], 'GL Map Accounts'[Account Group 1 Desc] = "Income"   )  

Income = CALCULATE (sum('GL Data'[Value]), 'GL Map Accounts'[Account Group 1 Desc] = "Income"   )  

[v-vpabbu]

Hi @Andrew-HLP,

It looks like the Calculation Group might be overriding the filter context. You could try modifying your Calculation Group formula to include the KEEPFILTERS function to help preserve the existing filters.

If the suggested solution doesn't resolve your issue, please share sample data or a PBIX file.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,
Vinay Pabbu

[Andrew-HLP]

Thank you, but unfortunately I couldnt get this to work.

I am having a few other issues with filter context (https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Row-amp-Filter-Context-DAX-formula/m...) so once I resolve these I will try again.

Thanks fo ryour help.

Regards

Andrew

[v-kathullac]

Hi @Andrew-HLP 

Could you please confirm if this issue has been resolved? If it has, kindly mark the helpful reply and "Accept as a Solution".And additionally give 'Kudos'
This will assist other community members in solving similar problems more quickly.

Thank you.
Chaithanya.

[Andrew-HLP]

Hello - thanks for following up.  It is still open.  I will post some more updated information in the next few days.  Thank you.

[v-kathullac]

Hi @Andrew-HLP 

Could you please confirm if this issue has been resolved? If it has, kindly mark the helpful reply and "Accept as a Solution".And additionally give 'Kudos'
This will assist other community members in solving similar problems more quickly.

Thank you.
Chaithanya.

[Andrew-HLP]

Hello - thanks for following up.  It is still open.  I have posted some additional information asking for further help - see problems 1 and 2 above.  Thank you.