Show Sum of Max Values in Subtotal Row within a Matrix

[hashari]

I have a Matrix that is used to show "Maximum" counts that appear across multiple weeks of data (Matrix below shows Weeks 1-9).

I am simply selecting 'Maximum' as the aggregation type which gives me my desired values for each of the individual weeks.

However, for my Total at the end, I want to see the Sum of the Max values in each row.

So, instead of 6 appearing for Total on each row (which is the maximum value for any column on that row), I want the Net Sum of the Max values to appear instead.  

In this case, 23 should appear as the Total value for each row [6+1+3+3+0+2+4+3+1]

I've tried several methods, but drawing blanks, help would be GREATLY appreciated!

[MFelix]

Hi @hashari ,

In this case you need to use a measure since the matrix works has a aggregation level it will show the maximum for the line you are at.

Try something similar to this:

MAXIMUM VALUE FOR MATRIX =
IF (
    HASONEVALUE ( Table[COLUMN OF MATRIX] ),
    MAX ( Table[VALUE OF MATRIX] ),
    SUMX ( VALUES ( Table[ROWS OF MATRIX] ), MAX ( Table[VALUE OF MATRIX] ) )
)

Replace the values of the columns by the ones on your model.

If this does not work can you share a sample of your model so I can make the adjustments.

[hashari]

Hi @MFelix 

So I tried what you suggested, but got an incorrect result.

Total shows 36 (instead of the expected value of 23), so not sure how to account for that.

Also, not sure if I can drop a PBI file here so I published the file to this Google Drive location if you could please take a look: Shared PBI File - Google Drive 

[MFelix · Accepted solution]

Hi @hashari ,

Try the following:

• Create a new measure to calculate the maximum value:

MAximumValue = MAX(PostsPerWeek2[cnt_other2])

• Create a second measure to calculate the total per line:

MAXIMUM VALUE FOR MATRIX =
VAR TEMPTABLE =
    ADDCOLUMNS ( VALUES ( PostsPerWeek2[fweek] ), "@MAximumValue", [MAximumValue] )
RETURN
    SUMX ( TEMPTABLE, [@MAximumValue] )

[MFelix]

You can also use the following measure:

MAXIMUM VALUE FOR MATRIX = 
SUMX(KEEPFILTERS(VALUES(PostsPerWeek2[fweek])), [MAximumValue])

[richbenmintz]

Hi @hashari,

Given your data model I believe the following measure will provide you with what you require

Sum of Max Measure = 
SWITCH(true(),
    HASONEVALUE(PostsPerWeek2[district]),sumx(SUMMARIZE('PostsPerWeek2', PostsPerWeek2[fweek], PostsPerWeek2[district], "max_c", MAX('PostsPerWeek2'[cnt_other2])), [max_c]),
    HASONEVALUE(PostsPerWeek2[region]), sumx(SUMMARIZE('PostsPerWeek2', PostsPerWeek2[fweek], PostsPerWeek2[region], "max_c", MAX('PostsPerWeek2'[cnt_other2])), [max_c]),
    HASONEVALUE(PostsPerWeek2[sdiv]), sumx(SUMMARIZE('PostsPerWeek2', PostsPerWeek2[fweek], PostsPerWeek2[sdiv], "max_c", MAX('PostsPerWeek2'[cnt_other2])), [max_c])
    , blank())

[hashari]

@richbenmintz 

@MFelix 

Thank you both for these follow-up solutions - they both worked brilliantly!

I have so much left to learn...

[richbenmintz]

Hi @hashari ,

You are most welcome!