Forum Discussion

Zaeem7's avatar
Zaeem7
Frequent Visitor
8 years ago
Solved

Dynamic Measure selection - Actual and Forecast

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 Tota...
Need Help
  • Stachu's avatar
    Stachu
    8 years ago

    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's avatar
Lethabo5
Frequent Visitor
6 years ago

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's avatar
Lethabo5
Frequent Visitor
6 years ago

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?