Lineage and filter expansion

[r17]

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!

[v-yuezhe-msft]

@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,

[r17 · Accepted 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])
)