Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
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:
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
44 | |
32 | |
30 | |
18 | |
17 |