Dynamic Measure selection - Actual and Forecast

[Zaeem7]

Hi,

I have a list of numerical columns directly coming from raw data as below:

Actual Total Promo Cost

ACTUAL Incremental GIV

ACTUAL Total Units

Actual Variable Costs

FCST Total Promo Costs

FCST Incremental GIV

FCST Total Units

FCST Variable Costs

I have listed a few, there are many more.

Now in all my Visuals/Expressions, i want to use different sets(Actual or Forecast) and don't need different visual for Actual and different for Forecast.

Help needed here is to have a selector like: [ ] Actual

                                                                      [ ] Forecast 

which will filter the list of measures as selected and give the user an option to select which type Measure from that filtered list they want to see.

I can use the usual way of creating a separate table with Measure names, linking it with my main table through the usual expression and selecting the Measure to show in visual through a slicer.

But i have to select different set of Measures in different visuals and expressions and that would force me to create mutiple slicers for each visual.

Any easy way to do this?

Thanks!

Zaeem 

[Stachu · Accepted solution]

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 measure

Total Promo Cost = SWITCH(SlicerValueHere,"Actual",SUM(Table[Actual Total Promo Cost]),"Forecast",SUM(Table[FCST Total Promo Costs],BLANK())

[Lethabo5]

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? 

[Lethabo5]

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?

[Anonymous]

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.

[Zaeem7]

Hi @Stachu,

Thanks a lot!

The Unpivot/ Pivot process works. As of now there aren't any other reporting work which would require me to keep data structure intact. So this is fine now 🙂

Thanks!

Zaeem