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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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