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 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 

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())

     

5 Replies

  • Stachu's avatar
    Stachu
    Icon for Community Champion rankCommunity Champion
    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())

     

    • Zaeem7's avatar
      Zaeem7
      Frequent Visitor
      8 years ago

      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 

    • 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? 

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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.