Forum Discussion
Lineage and filter expansion
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!
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])
)
2 Replies
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])
)
