Power BI Matrix Group Sub Total Row Gets Values From Source Data

[PineLeaf]

I am using the Power BI Matrix control as I like the expand/collapse and how subtotals displayed when collapsed.

My “source data” already contains rows which could be looked up to retrieve the correct subtotal values.

When a row is looked up there are about 50 columns I would to retrieve and display on the subtotal row.

I do not want to recalculate the values in Power BI (some of them are quite complex).

Can the Matrix “group row” – get its subtotals from correct “data row”.

For Example: if UI group level = “Filter Level 2” and get current value equals “defensive” get data row where filter level 2 = “defensive” and “filter level 3” is blank?

Or If UI Group Level = “Filter Level 3” and current value equals “Australian Fixed Income” get data row where filter level 3 = “Australian fixed income” and “level 4 is blank”

Can the formula pass the column name in as the column names are identical across the group total and data row - ie so 50 measures do not need to be created.

Alternatively

Can the row with the correct subtotal data be promoted as the sub-total row?

Example Below:

The correct value for “Defensive” should be -0.01% not -0.02% and can be seen in the data below.

The correct value for “Aus Fixed Income” should be 0.00% not -0.01%

Matrix Report at the moment – shows totals on a separate line and does not show sub-totals when collapsed

Raw Data looks like this

[ImkeF]

Hi @PineLeaf ,
there is a good chance that calculation groups can do this: Creating Calculation Groups in Power BI Desktop - SQLBI

[PineLeaf]

Hey thanks - that video references creating new measures to do it (ie 50 measures). Is there a way to make such logic work on a row level rather than per column?

[ImkeF]

Hi @PineLeaf ,
yes, that's possible using the ISINSCOPE function.
Check out this video on how to apply: How to Choose a Different Aggregation (than SubGroup) in Subtotals | Power BI - YouTube