Change formulas variables in an user friendly way without modifying the formula

[YannDV]

Hi! 

 I am currenlty building a power bi dashboard for a profitability report and I was wondering if it was possible to change a formula in an user freindly way. Let me explain you what I mean by a concrete example. 

I have 4 different types of costs, called CM, CM1, CM2, CM3. 

The total of these cost is the total cost of a product. 

Each cost is calculated from a different formula, for example 

- CM = Raw Materials + Mold + Energy 

- CM1 = Payroll Employees+ Packaging

I would like to know if there could be an easy way for the final user to change the variable, for example :

=> User wants to put packaging in CM

and therefore have =

-CM = Raw materials+Mold+Energy+Packaging 

-CM1 = Payroll Employees

But without having change the formulas in power bi. 

Do you think it is possible ? 

Thank you a lot and I wish you a good day 

[ValtteriN · Accepted solution]

Hi,

You could use either multiple slicer tables or one and utilize EXCEPT.

Start data:

'MaterialCostTable'

'Slicer CM1'

Data model:

Dax:

CM1 = CALCULATE(SUM(MaterialCostTable[Cost]),

USERELATIONSHIP('Slicer CM1'[Material],MaterialCostTable[Material]))

CM2 = CALCULATE(SUM(MaterialCostTable[Cost]),

USERELATIONSHIP('Slicer CM2'[Material],MaterialCostTable[Material]))

CM2 Alternate = CALCULATE(SUM(MaterialCostTable[Cost]),

EXCEPT(all('Slicer CM1'[Material]),ALLSELECTED('Slicer CM1'[Material])),

//If you use this method you can have active relationship between the slicer and remove USERELATIONSHIP here

USERELATIONSHIP('Slicer CM1'[Material],MaterialCostTable[Material]))

End result:

Explanation:
So in my example I have to different ways of doing this. You can use multiple slicers (as seen on the picture) and select each cost you want to include in the calculation. 
The second option is to use one slicer and in this alternate example [CM2 alternate] includes all of the selections not selected in CM1 slicer (As you can see CM2 and CM2 alternate return the same result using different method).

I hope this helps to solve your issue and if it does consider accepting this as a solution and giving the post a thumbs up! 

[YannDV]

Thank you very much! Perfect solution! What a master 😍

[amitchandak]

@YannDV , No, I do not think viewers can create an on-the-fly measure.

Use can create new measures in Edit mode if they have access.