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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DenisSipchenko
Advocate III
Advocate III

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

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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors