Powerpivot value separation depending on criteria

[Henceman]

I have two tables, a data table and a lookup table.

Data table

Account	PG	Value
EFG332332	1540	100
EFG332332	1840	100
HER44122	1560	10

Lookup table

Account	Corporate or PG
EFG332332	Corprorate
HER44122	PG

Pivot I need, would create the result roughly like that

Account	PG	Value
EFG332332		200
HER44122	1560	10

As you see, PG for first line item would be left empty, since it is not required to be shown in the pivot (forbidden actually), so if there are multiple PGs on same account, then pivot would simply need to summarize the values, but if lookup table states the data needs a "PG" level, the pivot would need to pick up the data from the data table and populate the pivot as well.

And it all needs to happen in the same column, so some of the accounts would feature empty cells for PG and some have the data.

I hope my explanation was understandable and doable

Thanks.

H.

 

[Matt Allington]

Yes, it’s doable. Put account and Corporate or PG columns on rows in the pivot, set it to a tabular layout, and write a measure that returns a value only for PG, something like

CALCULATE(SUM(DATATABLE[value]),keepfilters(lookuptable[corp or pg]=“PG”))

 

[Henceman]

This looks promising! If my lookup table features more criteria, like some accounts are related to Balance sheet and sign needs to be reversed? This time the criteria would need to alternate, so if first account is mapped as balance sheet, then value would need to be negative and if PL- positive.