cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I

## Show Sum of Max Values in Subtotal Row within a Matrix

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!

1 ACCEPTED SOLUTION
Super User

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] )``````

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

7 REPLIES 7
Super User

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.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Helper I

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

Super User

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] )``````

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Super User

You can also use the following measure:

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Solution Sage

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())``````

I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!

Helper I

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

I have so much left to learn...

Solution Sage

Hi @hashari ,

You are most welcome!

I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors