Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

SUM measure for multiple partitions

 

 

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:

DenisSipchenko_0-1680012974401.png
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):

DenisSipchenko_2-1680013753565.png

Please help to fix if possible.
Source (Download)

 



 

5 REPLIES 5
johnt75
Super User
Super User

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
Anonymous
Not applicable

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

DenisSipchenko_0-1680078527927.png

 

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

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.

Anonymous
Not applicable

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!