Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Dear Experts,
My source data is as below. I want to SUM(Table[Value]) per BU, but for all non BU Common I need to include Values of the Employees that appear in BU Common.
BU | Employee | Value |
BU Common | 1 | 1 |
BU Common | 3 | 2 |
BU 1 | 1 | 3 |
BU 1 | 1 | 4 |
BU 1 | 2 | 5 |
BU 2 | 1 | 6 |
BU 2 | 3 | 7 |
A simple pivot with BU on rows should return
BU 1: 13 (3+4+5+1)
BU 2: 16 (6+7+1+2)
The measure I struggle with:
CALCULATE( SUM(Table[Value], ALL( Table[BU] ), UNION( VALUES( Table[BU] ), ROW("BU", "BU Common") ), VALUES( Table[Employee] )
I already see the problem with lost lineage due to UNION and ROW, but can't come up with an alternative solution.
What if instead of simple SUM all rows (3, 4, 5, 1 for BU 1) should be returned?
Would appreciate any help on this.
Thank you!
Solved! Go to Solution.
Thank you!
I've also come up with the following solution
CALCULATE(
Table[Value];
FILTER(ALL(Table[BU]);Table[BU] IN VALUES(Table[BU]) || Table[BU] = "BU Common");
VALUES(Table[Employee])
)
@r17,
Firstly, pivot columns as follows.
Secondly, create the following columns in your table.
BU1TOTAL = IF(NOT(ISBLANK('Table'[BU 1])),'Table'[BU 1]+'Table'[BU Common],0)
BU2TOTAL = IF(NOT(ISBLANK('Table'[BU 2])),'Table'[BU 2]+'Table'[BU Common],0)
Regards,
Thank you!
I've also come up with the following solution
CALCULATE(
Table[Value];
FILTER(ALL(Table[BU]);Table[BU] IN VALUES(Table[BU]) || Table[BU] = "BU Common");
VALUES(Table[Employee])
)