Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Consolidating multiple measures into a single user defined measure

I am creating a dashboard that compares Actual to Forecast values. Several times a year a new forecast is released. For example Forecast 2 in the example below.

 

Currently, I have a seperate measure for each version of the forecast. For example

 

1) Forecast1(Measure) = SUMX(FILTER(SUMMARIZE(VALUES(DimDate[Date]),[Date],"ABCD",[Actuals(Measure)],"EFGH",CALCULATE(SUM('Revenue'[Value]), 'Revenue'[ValueType]= "Forecast 1")),[ABCD]>0),[EFGH])


2) Forecast2(Measure) = SUMX(FILTER(SUMMARIZE(VALUES(DimDate[Date]),[Date],"ABCD",[Actuals(Measure)],"EFGH",CALCULATE(SUM('Revenue'[Value]), 'Revenue'[ValueType]= "Forecast 2")),[ABCD]>0),[EFGH])

 

The issue with this "hardcoding" approach is that when a new forecast released (E.g Forecast3(measure), I need to write a new DAX formula . For example if forecast 3 came out the measure would look like

  

3) Forecast3(Measure) = SUMX(FILTER(SUMMARIZE(VALUES(DimDate[Date]),[Date],"ABCD",[Actuals(Measure)],"EFGH",CALCULATE(SUM('Revenue'[Value]), 'Revenue'[ValueType]= "Forecast 3")),[ABCD]>0),[EFGH])

 

How can I surface a new version of the forecast without "Hardcoding" the name of the forecast  version into the DAX? For example  is it possible to  user a slicer, so that the end users can define the version of the forecast is presented?

 

Any suggestions would be much appreciated.  PBIX file here

 

Cheers 

Steve

Need Help

8 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thanks for the pbix file.  Its very close to what I am after.

       

      However, one thing I am trying to avoid, is any hardcoded DAX that includes the forecast name.  The forecast names are created by finance, on an adhoc basis.  The challenge is - we don't know what the forecasts will be called, or when the new forecasts will be released (they just appear in the underlying datasource).  If we need to use  hardcoded DAX such as what we have in the SWITCH formula (below), we will need to manually update the DAX with the new forecast name when a new forecast is released  (e.g JanReforecast in the example below). 

       

      Forecast type chosen = SWITCH([Forecast number chosen],1,"Forecast 1",2,"Forecast 2, 3,"JanReforecast")

       

      Any ideas on avoiding the hardcoding of the forecasts in the SWITCH forumula?


      Cheers

      Steve

       


      Cheers

      Steve

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        8 years ago

        I have no ideas now.  If there is no uniformity in entering data, then i am sure no formula will work.

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    Hi Anonymous

     

    You should be able to.  Just create a new table in your data model and enter a row for every forecast.

     

    Don't relate this table to any of your existing tables. 

     

    Create a slicer using this new table to allow end users to make a slicer selection.

     

    Create a calculated measure that works out the MIN (or MAX) selection from this table.

     

    Use the new measure in your formula

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thanks Phil

      I don't fully grasp the proposed solution. 

      The two steps are fine 

      1) Creating an unrelated "Forecast Versions" table

      2) Surfacing the Forecast versions through a Slicers.

      However, I am running into problems with the last two steps

      3) Calculating a measure that works out the MIN (or MAX) selection from this table. I assume you mean something like   

                 

      Master_Measure = MIN('Forecast Versions'[Forecast]) 

      With this step, I am not sure what the function of the Min or Max is. 

       

      4) Use the new measure in your formula. In this situation I am interpeting this as 

       

      Select_Forecast = SUMX(FILTER(SUMMARIZE(VALUES(DimDate[Date]),[Date],"ABCD",[Actuals(Measure)],"EFGH",CALCULATE(SUM('Revenue'[Value]), 'Revenue'[ValueType]= Master_Measure)),[ABCD]>0),[EFGH])

      This produces an error. "Failed to resolve "Master_Measure. Its not a valid Table, Variable or function name. 

       

      Any suggestions, would be much appreciated.  Working file attached

       

      Cheers 

      Steve

      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        8 years ago

        HI Anonymous

         

        All the MIN or MAX does at step three is to help guarantee you return a single value.

         

        For step 4 you need square brackets around the measure name

         

        Select_Forecast = 
            SUMX(
                FILTER(
                    SUMMARIZE(
                        VALUES(DimDate[Date]),
                        [Date],
                       "ABCD",[Actuals(Measure)],"EFGH",CALCULATE(SUM('Revenue'[Value]), 
               'Revenue'[ValueType]= [Master_Measure])),[ABCD]>0),[EFGH])