topic Re: SUM measure for multiple partitions in DAX Commands and Tips

SUM measure for multiple partitions

Anonymous — Tue, 28 Mar 2023 14:34:43 GMT

How to create SUM measure on multiple partitions?

Example (that almost works as estimated)
Task: create measure, that will provide Sum of values based on 2 partitions (SubCategory1, SubCategory2)
Like on this screen:

Measure definition (thanks to AlexisOlson , details here )

m1 subCtgVal 1,2 = VAR Result = CALCULATE( SUM(MyTable[Value]), ALLSELECTED(MyTable), -- Removes RowContext Transition SUMMARIZE( MyTable, MyTable[SubCategory1], -- Partition1 MyTable[SubCategory2] -- Partition2 ) ) RETURN Result

Problem: it works as estimated only until you touch SubCategory3 filter.
When you use SubCategory3 filter, it behaves like 3 partition SUM ((SubCategory1, SubCategory2, SubCategory3):

Please help to fix if possible.
Source (Download)

Re: SUM measure for multiple partitions

johnt75 — Tue, 28 Mar 2023 15:21:50 GMT

m1 subCtgVal 1,2 = VAR Result = CALCULATE( SUM(MyTable[Value]), ALLEXCEPT(MyTable, MyTable[SubCategory1], MyTable[SubCategory2]), -- Removes RowContext Transition SUMMARIZE( MyTable, MyTable[SubCategory1], -- Partition1 MyTable[SubCategory2] -- Partition2 ) ) RETURN Result

Re: SUM measure for multiple partitions

Anonymous — Wed, 29 Mar 2023 08:29:02 GMT

Not as easy, unfortunately...
It shouldn't ignore slicer selection...
In following selection it should show 60, not 93:

Re: SUM measure for multiple partitions

johnt75 — Wed, 29 Mar 2023 08:45:26 GMT

The below seems to work

m1 subCtgVal 1,2 = VAR ChosenCats = ALLSELECTED( 'MyTable'[SubCategory1], 'MyTable'[SubCategory2], 'MyTable'[SubCategory3]) VAR Result = CALCULATE( SUM(MyTable[Value]), ALLEXCEPT( 'MyTable', 'MyTable'[SubCategory1], 'MyTable'[SubCategory2]), ChosenCats, SUMMARIZE( MyTable, MyTable[SubCategory1], -- Partition1 MyTable[SubCategory2] -- Partition2 ) ) RETURN Result

Re: SUM measure for multiple partitions

Anonymous — Wed, 29 Mar 2023 12:37:38 GMT

Works! 🙂

Just Instead of

VAR ChosenCats = ALLSELECTED( 'MyTable'[SubCategory1], 'MyTable'[SubCategory2], 'MyTable'[SubCategory3])

recommend to use

VAR ChosenCats = ALLSELECTED( MyTable )

It looks like more generic solution

Re: SUM measure for multiple partitions

AlexisOlson — Fri, 31 Mar 2023 16:26:15 GMT

The ALLEXCEPT seems redundant here since we already have the SUMMARIZE putting the partition filtering back.

How about this simplified version?

VAR ChosenCats = ALLSELECTED ( MyTable ) VAR Result = CALCULATE ( SUM ( MyTable[Value] ), ChosenCats, SUMMARIZE ( MyTable, MyTable[SubCategory1], -- Partition1 MyTable[SubCategory2] -- Partition2 ) ) RETURN Result

Edit: Hang on... This should be the same as the original measure. Let me dig in and see why that one isn't working as expected. Will update later.

Edit 2: The above appears to work fine. I think the difference is that ALLSELCTED inside of CALCULATE means transforming a shadow filter context into an explicit filter context rather than being used as a more typical table filter when we pass it in as a variable.