Help with aggregation in matrix for different total value

[SriRed123]

Hi,

I have table with below data, which I am trying to aggregate into a matrix

Store	Product	Day	Interim	Final
Store1	Prod1	Day1	10	20
Store1	Prod1	Day2	11	13
Store1	Prod1	Day3	7	3
Store1	Prod2	Day1	21	14
Store1	Prod2	Day2	11	20
Store1	Prod2	Day3	18	22

Resultant output should be matrix as below. Value of the matrix is sum of (max value of Interim and final) at individual product and day.

Eg: For Store1/Day1:  20(Greatest of Prod1/Day1 Interim and Final) + 21(Greatest of Prod2/Day1 Interim and Final) = 41

I am able to achieve the value by using the below measure

Quantity = SUMX (Table, IF(Table[Interim]>Table[Final],Table[Interim],Table[Final]))

	Day1	Day2	Day3	Total
Store1	41	33	29	103

However, ‘Total’ column should be sum of (max of aggregated Interim and aggregated final values at product level across all days)

Eg:    For Prod1, Interim sum across days is 31 and Final sum across day is 34. So the max value is 34

For Prod2, Interim sum across days is 22 and Final sum across day is 33. So the max value is 33

For Prod3, Interim sum across days is 25 and Final sum across day is 25. So the max value is 25

Total: 34 + 33 + 25 = 92.

Could you please help if there is any way of achieving this updated total in matrix.

Thanks

[Anonymous]

// T is your table.

[Unified Quantity] =
SUMX(
	VALUES( T[Product] ),
	CALCULATE(
		MAX(
			SUM( T[Interim] ),
			SUM( T[Final] )
		)
	)
)

If you slice and dice your data properly, the result should be what you want.

Best

D

[harshnathani]

Hi @SriRed123 ,

You can try this measure

Quantity =
SWITCH (
    TRUE (),
    ISINSCOPE ( 'Table'[ Day] ), SUMX (
        'Table',
        IF (
            'Table'[ Interim] > 'Table'[ Final],
            'Table'[ Interim],
            'Table'[ Final]
        )
    ),
    SUMX (
        SUMMARIZE (
            'Table',
            'Table'[Store],
            'Table'[ Day],
            'Table'[ Interim],
            'Table'[ Final]
        ),
        IF (
            MAX ( 'Table'[ Final] )
                >= MAX ( 'Table'[ Interim] ),
            'Table'[ Final],
            'Table'[ Interim]
        )
    )
)

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

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

[Anonymous]

Hello @SriRed123 

A measure like this one shoudl do the trick:

Measure = 
var _Store = SELECTEDVALUE(Data[Store])
var _Day = HASONEFILTER(Data[Day])
var _DayQty = SUMX (Data, IF(Data[Interim]>Data[Final],Data[Interim],Data[Final]))
var _TempTable = 
groupby(
SUMMARIZE(
Data,
Data[Store],
Data[Day],
"DayMax", max(sum(Data[Interim]), sum(Data[Final]))
),
Data[Store], 
"StoreTotal", SUMX(CURRENTGROUP(), [DayMax])
)
var _StoreQty = SUMX(FILTER(_TempTable,Data[Store] = _Store), [StoreTotal])


var result = IF(_Day, _DayQty, _StoreQty)  // controls wether to show day or store total

return 
result

I add a second store to check the results

I assumed that were you mention Prod1, Prod2 and Prod3 in the explanation of the Total calculation that you meant Day1, Day2, Day3.

Hope this helps,

Jan

if this is a solution for you, don't forget to mark it as such. thanks