Calculate MAX for subtotals

[Anonymous]

Hi,

Need assistance in calculating MAX as subtotals in a Matrix. 

In the below sample data set, I am trying to arrive at Subtotals for Packages (highlighted in red font) where subtotal for each package should be MAX across ALL packages of that Distributor.

Item 1
	Distributor A			18
		Package 1		18
			State 1	1
			State 2	2
			State 3	3
		Package 2		18
			State 1	2
			State 2	3
			State 3	4
		Package 3		18
			State 1	3
			State 2	4
			State 3	5
			State 4	6

PS - The data set contains multiple items, distributors and packages.

Thanks in advance!

[v-alq-msft]

Hi, @Anonymous 

The link provided is unavailable. I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a measure like below.

Result Measure = 
IF(
    ISINSCOPE('Table'[State]),
    SUM('Table'[Value]),
    IF(
        ISINSCOPE('Table'[Package]),
        SUM('Table'[Value]),
        IF(
            ISINSCOPE('Table'[Distributor]),
            MAXX(
                SUMMARIZE(
                    'Table',
                    'Table'[Item],
                    'Table'[Distributor],
                    'Table'[Package],
                    "Result",
                    SUM('Table'[Value])
                ),
                [Result]
            ),
            SUM('Table'[Value])
        )
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Allan @v-alq-msft ,

Sorry for the inconvenience, the link must've expired. New link - https://we.tl/t-LFy0jJ9yig

Thank you for the above measure. It partly solves the problem in hand. However the package and item subtotal still does not match the desired output!
I want the package subtotal to be MAX of all packages for that particular Distribtor. In other words, continuing to the above example, subtotal for Package 1, Package 2 and Package 3 should also be 36 when we have not drilled down upto State level information. And similarly subtotal for Package 4, Package 5 and Package 6 should be 48.
Additionally subtotal for Item 1 should be Max of Distributors which is 36 and likewise subtotal for Item 2 should be 48.
Would that be possible?
Thanks!

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

You may modify the measure like below. The pbix file is attached in the end.

Result Measure = 
IF(
    ISINSCOPE('Table'[State]),
    SUM('Table'[Value]),
    IF(
        ISINSCOPE('Table'[Package]),
        MAXX(
                SUMMARIZE(
                    FILTER(
                        ALL('Table'),
                        [Item]=MAX('Table'[Item])&&
                        [Distributor]=MAX('Table'[Distributor])
                    ),
                    'Table'[Item],
                    'Table'[Distributor],
                    'Table'[Package],
                    "Result",
                    SUM('Table'[Value])
                ),
                [Result]
            ),
        MAXX(
                SUMMARIZE(
                    'Table',
                    'Table'[Item],
                    'Table'[Distributor],
                    'Table'[Package],
                    "Result",
                    SUM('Table'[Value])
                ),
                [Result]
        )
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you so much Allan! 😎

[Anonymous]

https://we.tl/t-nGlBPRhNoN 

Link to download sample data in excel capturing the logic required behind subtotals and grandtotal with the desired output.  

[amitchandak]

@Anonymous , While I need more data for excat formula, You can use isinscope to get that

example

If(isinscope(Table[Distributor]), maxx(all(Table[Distributor]), [Value]),[Value])

refer this - https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

[Anonymous]

Hi @amitchandak,

Thanks for the suggestion! The if isinscope function gives me the following error - Too many arguments were passed to the ISINSCOPE function. The maximum argument count for the function is 1. Let me know if i'm missing something!