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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors