DAX: calculate dynamic table/rows that update on user slicers

[theotherside]

Hi community, this is my first post ever on the forum!

I have a transformed table named items (the data below is a mock up simplification to help with posting on a forum, but if I can solve the example below then I can probably solve my real problem)

transformed table: items

item	color	direction	qty
a	red	in	10
b	yellow	in	2
c	blue	out	200
d	yellow	out	40
e	yellow	out	20
f	red	in	35
g	multicoloured	in	1

I have lots of measures and visuals that help me slice and dice qty by the item, direction & color columns. That all works great. But I needs these visuals and/or measures to also pick up an additional two rows:

calculated table: items2

Item	color	direction	qty
X	multicoloured	in	= 2 * sum(items[qty]) where direction='in'
Y	multicoloured	out	= 2 * sum(items[qty]) where direction = out

The first three columns of items2 are hardcoded , the qty column needs to be calculated on the fly. the idea is that I can then create measures such as: combined_qty=items[qty] + items2[qty] (a mix of transfomreand still have the ability to slice and dice by color and direction

So why don't I just do this with power query, or a DAX calculated column? The reason is because I need the filters applied by the user (via slicers) on items to impact the calculation of items2[qty] on the fly. For example, if the user filters out red items, then the qty value for item X will be 6. (=3 * (2+1))

So why don't i just use measures, which automatically update on the fly with user interaction? The reason is because this returns a scalar value only, and not an entire row, but I need the visuals to be able to filter on items X and Y based on color and direction.

My last idea was a calculated table, but I got stuck trying to do that as it's a bit above my level of understanding. 

Is what I'm trying to achieve possible?

Thank you so much in advance!

[v-jianboli-msft]

Hi @theotherside ,

The calculation table does not change according to your filtering, try using measure:

First union the two table:

Then apply the measure to the table visual:

QTY Measure = 
var _a = ALLSELECTED('Union Item'[color])
var _b = 
SWITCH(SELECTEDVALUE('Union Item'[item]),
"X",CALCULATE(2*SUM('Union Item'[qty]),FILTER(ALL('Union Item'),[direction]="in"&&[color] in _a)),
"Y",CALCULATE(2*SUM('Union Item'[qty]),FILTER(ALL('Union Item'),[direction]="out"&&[color] in _a)),
SUM('Union Item'[qty]))
return _b

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[theotherside]

Hi @v-jianboli-msft, 

Thank you so much for your reply it has been incredibly helpful! I am sorry for the delay in my reply to you.

I have tried your solution. If I use your second screenshot as an example, the QTY Measure is correctly calculating which is great. However what I was hoping is that the total of QTY Measure, rather than being 246, would actually be 738. So in this way the user can filter by whatever columns they like, and not only will the QTY Measure of X and Y update dynamically, but any sum aggregation of QTY Measure will also update dynamically. Is this possible?

I know this is pointless for my colour/direction/qty example, but for my real world application this is crucial.

Thank you again!

Simon

[v-jianboli-msft · Accepted solution]

Hi @theotherside ,

Please try:

QTY Measure = 
var _a = ALLSELECTED('Union Item'[color])
var _b = SUMX('Union Item',SWITCH([item],
"X",CALCULATE(2*SUM('Union Item'[qty]),FILTER(ALL('Union Item'),[direction]="in"&&[color] in _a)),
"Y",CALCULATE(2*SUM('Union Item'[qty]),FILTER(ALL('Union Item'),[direction]="out"&&[color] in _a)),
CALCULATE(SUM('Union Item'[qty]))))
return _b

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[theotherside]

Thank you so much this work as expected! Thank you for your help!