Forum Discussion
Dynamic Measure selection - Actual and Forecast
if you can change the undering data table the problem is quite simple:
1) you unpivot existing measures (so they're in rows)2) split the new column to separate the measure type (act/fcst) and measure description
3) pivot the measures only
this way you have 4 measures with new attribute determining act/fcst split
if you want to keep the data structure intact, then SWITCH will do the job. You will need the slicers for act/fcst flag, and define scenario for each measureTotal Promo Cost = SWITCH(SlicerValueHere,"Actual",SUM(Table[Actual Total Promo Cost]),"Forecast",SUM(Table[FCST Total Promo Costs],BLANK())
Community Championif you can change the undering data table the problem is quite simple:
1) you unpivot existing measures (so they're in rows)
2) split the new column to separate the measure type (act/fcst) and measure description
3) pivot the measures only
this way you have 4 measures with new attribute determining act/fcst split
if you want to keep the data structure intact, then SWITCH will do the job. You will need the slicers for act/fcst flag, and define scenario for each measure
Total Promo Cost = SWITCH(SlicerValueHere,"Actual",SUM(Table[Actual Total Promo Cost]),"Forecast",SUM(Table[FCST Total Promo Costs],BLANK())
Hi Stachu Does the Switch method work if you are connected to a cube as your data source? I see you say that can be used to keep the data structure intact. If yes, how can I go about implementing this? Would I use the measures created using SWITCH in my slicers?
Hi,
In my org, the powerbi file connected to a cube doesnt allow us to create a "new table"/"Enter Data" which makes it having switch function impossible to use.
If you can create New table by going to Home-->Under Data menu "Enter Data" then you can create table (for example name: Metrics) with values you want to switch between. In this original post, there is a need to swtich between Actual & Forecast. so enter these as values of the Table.
Next create measure like below :
Switch(True(), "Actual" in allselected(Metrics[values]),sum(<Actual column>),
sum(<Forecast column>))
Hope this helps.
Thank you for such a quick response!
I have the same issue with our cube connection, we cannot create tables, columns etc.
Is there an alternative solution?