Consolidating multiple measures into a single user defined measure

[Anonymous]

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

[Ashish_Mathur]

Hi,

You may refer to my solution in this file.

Hope this helps.

[Anonymous]

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]

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

[Phil_Seamark]

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]

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]

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

[Anonymous]

Thanks for the suggestion. I am still getting an error with the square brackets. I don't fully understand the reason for the error, as I understood the the inclusion of the FILTER expression should address this error.    

[Phil_Seamark]

Hi @Anonymous

Is this better?

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