Save subtotal in measure and use as dynamic multiplier

[padinator]

Hello all, to illustrate my issue i would like to show the following screenshot

As you see i have basically 3 categories of costs which is Picking (here i add up any cost except my storage costs) and the other 2 dimensions are costs for storage&unmoved storage cost.

Long story short, i am desperately looking for a way to dynamically save the cost amounts for (Storage & Unmoved) in some kind of measure which is then used to be multiplied by the corresponding "Spread" in any line. It is important to understand that this is just one block of many within my pivot table, which means i have about 100 blocs of Picking | Storage & Unmoved sums, while for any of these blocs i need to find a way to dynamically store its corresponding amount for Storage in a variable which is then used within the lines above to multiply the spread with it.

Any help or hint is highly appreciated!

Thanks !

[v-shex-msft]

Hi @padinator,

Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[amitchandak]

@padinator , check if measure like this can help

calculate(sum(table[COST]), filter(allselected(Table[Category]), Table[Category] in {"Storage","Unmoved"} ) )

or

calculate(sum(table[COST]), filter(all(Table[Category]), Table[Category] in {"Storage","Unmoved"} ) )

[padinator]

Wll unfortunately it does not work. Idk why but generally it does not seem that any of the formulas gets even accepted. I get an #ERROR in nearly any formula i am trying to use. I should mention that i use PowerPivot and not PowerBI , but regarding the syntax this should not make a difference?