Total Sum of Max by each category

[RustyNails]

Hello, I need help with something I'm stuck on. I'm looking to sum the total max values of each category that itself is a summation.

Here's my sample table: It has four columns, order_cd, part, cost_type and rate. NOTE: Sum of Part and Max of Order_cd are my measures. Also please keep in mind that we dont have only 3 different cost_types. This is just a sample. Real data will have hundreds of orders, hundreds of parts, thousands of cost types with their own rates.

Order_Cd	Part	cost_type	rate	Sum of Part	Max of Order_Cd
A0001	Steel Bolt	Metal	625	13,817	13,817
A0001	Steel Bolt	Welding	3,750	13,817	13,817
A0001	Steel Bolt	Packaging	9,442	13,817	13,817
A0001	Iron Case	Metal	441	9,753	13,817
A0001	Iron Case	Welding	2,647	9,753	13,817
A0001	Iron Case	Packaging	6,665	9,753	13,817
B0002	Metallic Frame	Metal	417	9,211	9,753
B0002	Metallic Frame	Welding	2,500	9,211	9,753
B0002	Metallic Frame	Packaging	6,294	9,211	9,753
B0002	Iron Case	Metal	441	9,753	9,753
B0002	Iron Case	Welding	2,647	9,753	9,753
B0002	Iron Case	Packaging	6,665	9,753	9,753
		Total_measure			23,570

What I want to do first is get a Totals of each Part (Sum of Part), which I did, and then to get Max of Order Cd:

Max of Order Cd =

var v1 = MAXX(SUMMARIZE('Table','Table'[Order_Cd],'Table'[Part],"amount",SUM('Table'[rate])),[amount])

return v1

This gets me almost where I need to be:

Order_cd	Max of Order_cd
A0001	13,817
B0002	9,753

All I want to do now is Sum the Max of Order_cd, which is where I'm stuck. I have:

Total_measure

= SUMX(SUMMARIZE('Table','Table'[Order_Cd],"max of sum",v1),[max of sum])

But this is summing it incorrectly. Please let me know what i'm missing, thank you!

[amitchandak]

I am not sure why are u using v1

Try

Total_measure
= SUMX(SUMMARIZE('Table','Table'[Order_Cd],"max of sum",[Max of Order Cd]),[max of sum])

Total_measure
= SUMX('Table',,[Max of Order Cd])

[RustyNails]

Hello, I tried both the total_measure measures,

the first one gives total of : 27,634

the second one gives total of : 165,804

I need them to add up to 23,570

[V-pazhen-msft · Accepted solution]

@RustyNails 
I used the following two measures, hope it helps you.

Sum of Part = CALCULATE(SUM('Table'[rate]),ALLEXCEPT('Table','Table'[Part],'Table'[Order_Cd]))


Total measure =
SUMX (
    SUMMARIZE (
        'Table',
        'Table'[Order_Cd],
        "sum of part", [Sum of Part],
        "max of order cd", CALCULATE (
            MAXX ( 'Table', [Sum of Part] ),
            ALLEXCEPT ( 'Table', 'Table'[Order_Cd] )
        )
    ),
    CALCULATE (
        MAXX ( 'Table', [Sum of Part] ),
        ALLEXCEPT ( 'Table', 'Table'[Order_Cd] )
    )
)

 

Paul Zheng
Don't forget to hit THUMBS UP and mark it as a solution if it helps you!