Matrix Visual - Row Grand Total

[Anonymous]

Hi all!

I have managed to create a Matrix visual that looks like the following:

Group 1 - Group 3 are columns made out of measure that calculates percentage amount from the value in 'Sum of Set-off / payment amount' column.

In the rightmost part of the visual, there is the grand total columns for each of the rows:

Why the Group 1 to Group 3 are not summed? Is there a way to sum the Group columns?

Best regards,

Bona

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

You can try to create another 3 new measures as below, and put them on the matrix to replace the original measures [Group 1], [Group 2] and [Group 3].

Ngroup1 =
SUMX (
    VALUES ( 'Date'[Fiscal year] ),
    SUMX ( VALUES ( 'Tablex'[Obligation type] ), [Group 1] )
)

Ngroup2 =
SUMX (
    VALUES ( 'Date'[Fiscal year] ),
    SUMX ( VALUES ( 'Tablex'[Obligation type] ), [Group 2] )
)

Ngroup3 =
SUMX (
    VALUES ( 'Date'[Fiscal year] ),
    SUMX ( VALUES ( 'Tablex'[Obligation type] ), [Group 3] )
)

Best Regards

[Anonymous]

Thank you so much for your help!

The updated formula now worked as intended.

[Anonymous]

Hello!

Sorry for reverting back just now.

I tried creating a new measure with the codes from your post, but this error message popped up:

For your reference, this is the list of fields available in my dataset:

And this is how I write the formula for Group 1 - Group 3:

Group 1 = SELECTEDVALUE(Sheet1[Set-off / payment amount]) * 0.25

Group 2 = SELECTEDVALUE(Sheet1[Set-off / payment amount]) * 0.35

Group 3 = SELECTEDVALUE(Sheet1[Set-off / payment amount]) * 0.32

What should I do to prevent the error message? I think this is caused by the 'Date' and 'Tablex', but I am not sure.

[v-yiruan-msft]

Hi @Anonymous ,

Yes, you are right. The table 'Date' and 'Tablex' not exist in your model, your table name is "Sheet1"... Please update the formula of measure as below:

NGroup1 =
SUMX (
    GROUPBY ( 'Sheet1', 'Sheet1'[Fiscal year], 'Sheet1'[Obligation type] ),
    [Group 1]
)

NGroup2 =
SUMX (
    GROUPBY ( 'Sheet1', 'Sheet1'[Fiscal year], 'Sheet1'[Obligation type] ),
    [Group 2]
)

NGroup3 =
SUMX (
    GROUPBY ( 'Sheet1', 'Sheet1'[Fiscal year], 'Sheet1'[Obligation type] ),
    [Group 3]
)

Best Regards