Adding Totals to Matrix visual

[amit_maurya]

[Anonymous · Accepted solution]

Hi  @amit_maurya ,

I created some data:

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
SUMMARIZE(
    'Table','Table'[Group],'Table'[Month],'Table'[Value])

Table 3 =
var _today=TODAY()
var _table=
SUMMARIZE(
    'Table 2',
    'Table 2'[Group],
    "Month","YTD Total",
    "Value",
    SUMX(FILTER(ALL('Table'),'Table'[Group]=EARLIER('Table 2'[Group])&&'Table'[Date]>=DATE(2022,7,1)&&'Table'[Date]<DATE(2023,3,1)),'Table'[Value]))
return
UNION(
    'Table 2',_table)

Table 4 =
var _today=TODAY()
var _table=
SUMMARIZE(
    'Table 3',
    'Table 3'[Group],
    "Month","YTD To Go Total",
    "Value",
    SUMX(FILTER(ALL('Table'),'Table'[Group]=EARLIER('Table 3'[Group])&&'Table'[Date]>=DATE(2023,3,1)&&'Table'[Date]<DATE(2023,7,1)),'Table'[Value]))
return
UNION(
    'Table 3',_table)

2. Create measure.

Measure =
IF(
    HASONEVALUE('Table 4'[Month]),MAX('Table 4'[Value]),
    SUMX(
        FILTER('Table 4',
        'Table 4'[Month] in {"YTD Total","YTD To Go Total"}),[Value]))

3. Matrix visual – Column subtotals – Column -- Modify Characters “Grand Total”.

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[PijushRoy]

Hi @amit_maurya 

Please create measure for July, Aug, Sep, Oct, Nov, Dec, Jan, Feb, Mar, April, May & June
Then create measure for YTD Total = July+...+Feb
YTD to Go = Mar + June
Grand Total = YTD Total + YTD to go

Bring all measure in Matrix table Value field