Sum of Max Values Displayed in Matrix

[mlinton3]

I've got a rather odd situation. I need to show the total quantity completed to date, which requires me to summate the entered punches for each order. I want to be able to slice the data, and show the max of an order by date (max of summation of punches up to that date). 

To complete this, I tried to create a Qty To Date Column, that summates the current punch and all previous for that order. To get it to display correctly, I have to use the Max function on this value in the matrix. This then only displays the max of my max Qty to Date in the grand/sub totals. I want it to display the sum. 

So essentially I need a way to display the sum of each rows max as the grand/sub total, within the date sliced range.

This is an example data set:

Example Data

Below is an excel model of what I'm getting currently total wise (highlighted) vs. my desired value (above in bold).

I'M CURRENTLY GETTING THE HIGHLIGHTED IN YELLOW TOTAL (MAX OF MY QTY. TO DATE DUE TO HAVING TO USE MAX OF QTY TO DATE VALUE). I would like to be able to use "sum" of qty to date to get the totals to be the nonhighlighted values.

Not sure how to make this happen.

Thanks,

M

[mlinton3]

To elaborate, I DO NOT want the highlighted values, which is what I'm currently getting. I want the summation of the max value, as displayed in the Totals row. 

[mahoneypat]

Please try this expression.  It results in the expected totals with the slicer.

Sum of Max =
SUMX ( VALUES ( 'Order'[Order] ), CALCULATE ( MAX ( 'Order'[Qty to Date] ) ) )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[mlinton3]

This is still giving me the incorrect value. I've already figured out the proper way to get my max qty to date.... the issue is that this max is summated when I put it in to the matrix, and it adds up each max to give me a qty much larger than my order qty...

If I set the matrix to only take my maximum value.... the grand totals on the bottom row only display the max or my order quantities... not the sum. 

I need the max value to date determined by the slicer, but then the total summation to add up correctly. 

[mahoneypat]

Please try this expression

New Measure =
SUMX (
    SUMMARIZE ( 'Order', 'Order'[Date], 'Order'[Order] ),
    CALCULATE ( MAX ( 'Order'[Qty to Date] ) )
)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[mlinton3]

MahoneyPat,

This just gives me the same value as my Qty to Date column. When added to my matrix, it summates to essentially double my actual order qty. If I change the field to only have the max, it still only shows the Max order qty in the total, which is my problem. 

[v-xuding-msft]

Hi @mlinton3 ，

Please try this:

Max Qty to Date = MAXX('Order',[Qty to Date])

[mlinton3]

I don't want the Totals row to contain the Max, I want it to contain the sum. Your expression only finds the max of all my orders. I need it to be date (through slicer) and order # specific. 

[parry2k]

@mlinton3 here is the measure for quantity completed and you can do the same for others

Measure 2 = 
SUMX ( 
SUMMARIZE ( 'Order', 'Order'[Date], 'Order'[Order], "__s", SUM ( 'Order'[Qty C] ) ), MAX ( [__s] ) 
) 

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[mlinton3]

Parry2k,

What's the Sum('Order'[Qty C])? It's not liking Sum to be in the formula for summarize? 

Max([_s])? What's the "_s"?

Hope you're close!