Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
hashari
Helper I
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!

 

MaxSums.jpg

1 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_0-1630662596579.png

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

7 REPLIES 7
MFelix
Super User
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


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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 

 

BeforeAfter.JPG

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_0-1630662596579.png

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



You can also use the following measure:

 

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

MFelix_0-1630665320798.png

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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!


@richbenmintz 

@MFelix 

 

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

I have so much left to learn...

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!


Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.