What If Analysis with Multiple Columns

Question

&nbsp; &nbsp; I have a fact table&nbsp;Date&nbsp; &nbsp; &nbsp; &nbsp; Category&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Qty&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Price&nbsp; &nbsp;Jan 1&nbsp; &nbsp; &nbsp; &nbsp; Biscuits&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;------ Say MondayJan 2&nbsp; &nbsp; &nbsp; &nbsp; Fruit&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;------ Say TuesdayJan 2&nbsp; &nbsp; &nbsp; &nbsp; Biscuit&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;------ Say TuesdayJan 4&nbsp; &nbsp; &nbsp; &nbsp; Cake&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-----&nbsp; &nbsp;ThursdayJan 8&nbsp; &nbsp; &nbsp; &nbsp; Fruit&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 8&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;------ MondayJan 8&nbsp; &nbsp; &nbsp; &nbsp; Protien Bar&nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;------ Monday&nbsp;I have Generated Series Tables for Each Category where Users can Select PriceThis is a single select dropdown Slicer with range -1 to 100I created a measure which gives default value from fact table if drop down is -1 else selected value for each Category&nbsp;I need a measure which will Give me total sale amount (Qty * Price)&nbsp; by multiplying user selected Price value from the single select dropdown.&nbsp;&nbsp;If I select Biscuit Price as 7 and Fruit 10&nbsp;&nbsp;My Data needs to be displayed&nbsp;Rows - Week Start Date&nbsp;Columns - Day Name in the week&nbsp;&nbsp;I need Output In below Format&nbsp;Week Start Date&nbsp; &nbsp; &nbsp; &nbsp;Monday&nbsp; &nbsp;Tuesday&nbsp; &nbsp;Wed&nbsp; &nbsp;Thursday&nbsp; &nbsp;Fri&nbsp; &nbsp; &nbsp; Sat&nbsp; &nbsp; &nbsp; SunJan - 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 35&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 105&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 18&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;Jan - 8&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 110&nbsp;&nbsp;&nbsp;&nbsp;

amitchandak · Answer

GR83 , Try a measure like 
&nbsp;
Sum( Table, Switch([Category],
"Fruit", selectedvalue(FruitParam[Value]) ,
"Biscuit",, selectedvalue(BiscuitParam[Value]) ,
// add others
) * [Qty]*[Price])

gr83 · Answer

I how do I use Table in Sum and Category In Switch?

Forum Discussion

What If Analysis with Multiple Columns

2 Replies

Related content

Cohort analysis

Sequential Order column based on multiple criteria

Multiple line and clustered columns chart

Retention analysis

Summarize Multiple Columns