scenario development with 3 price options

[Anonymous]

I want to create a stacked bar chart by product of total cost ( = SUMX quantity * price).  However, I have 3 price structures and I would like to have a slicer that would enable the user to select the price struture to see how it changes the total cost and by product.  Do you have any advice on how to set this up?  Thanks much. 

[v-lili6-msft · Accepted solution]

HI, @Anonymous

There are two ways for you as reference:

1. In Edit Queries, select three price columns, then Unpivot columns

Then use Attribute column as a slicer and total measure=SUMX( Qty* Value)

2. Create a price table

then create three total cost measure

total cost A = SUMX(Table1,Table1[Qty]*Table1[PriceA])

total cost B = SUMX(Table1,Table1[Qty]*Table1[PriceB])

total cost C = SUMX(Table1,Table1[Qty]*Table1[PriceC])

and use Price from Price table as a slicer and use this formula as a total result

Measure selection = SWITCH(TRUE(),
SELECTEDVALUE('Fact'[Price])="PriceA",[total cost A], 
SELECTEDVALUE('Fact'[Price])="PriceB",[total cost B],
SELECTEDVALUE('Fact'[Price])="PriceC",[total cost C],
BLANK())

Result:

 and here is pbix file, please try it.

Best Regards,

Lin

[Anonymous]

Dear Lin,  This is PERFECT!  Thanks much.  

I have 1 follow up question:  I see in your sample file, Table1 (2) that "Qty" and "Value" are calculated.  I would like to see formula/functions you used to create these.  Is there any way to see those directly in the file.  If not, could you please share the DAX formula?  Thanks again. 

Robert

[v-lili6-msft]

hi, @Anonymous

This is Unpivot columns Function in Eidt Queries.

https://support.office.com/en-us/article/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f588221c7098

http://radacad.com/pivot-and-unpivot-with-power-bi

Click Home->Edit Queries into Power Query. and you will see the steps

Best Regards,

Lin