1

Building upon the solution given by @olly (Power BI: How to scenario analysis, where the selector "looks up" the select value from slicer and gets values from that row) & file: https://pwrbi.com/so_55281950-2/

In the sample file a "what if" or scenario analysis is created. Two slicers are used, one which selects the scenario, and another one which selects the objects to apply the scenario on. @Olly provided a clever solution to deal with the situation:

Value + Trend = 
SUMX ( 
    'Demo Fact Table';
    'Demo Fact Table'[Value] * 
        ( 1 +
            IF ( 
                ISFILTERED ( 'Item Chooser'[Item] ) &&
                CONTAINS ( 
                    'Item Chooser';
                    'Item Chooser'[Item];
                    'Demo Fact Table'[Item]
                ) && 
                HASONEVALUE ( 'Scenario - Trend'[Category] );
                VALUES ( 'Scenario - Trend'[Trend Rise] );
                'Demo Fact Table'[trend_default]
            )
        )
    )

Building upon this solution I've been trying to apply the same logic, but to my specific problem. In my problem I don't only have a single layer of "categories", but instead hierarchy of 3 levels. How would I go on about applying the same solution if my categories had hierarchies? So from my slicer I would select three things: Category1, Year and Category2, which would yield the trend_rise for the selection. and would apply this apply this trend_rise on the selected (4th slicer) rows, i.e. (item A,B or/and C)

category1      -   Year - Category2 - trend rise
POSITIVE-trends    2018   low         5%
POSITIVE-trends    2018   high        5%
POSITIVE-trends    2017   low         5%
NEGATIVE-trends    2017   very high   -5%
NEUTRAL-trends     2018   low         0%
POSITIVE-trends    2018   high        5%
NEUTRAL-trends     2017   low         5%
NEUTRAL-trends     2016   very high   15%
Anton
  • 581
  • 1
  • 5
  • 23

1 Answers1

2

You only need a small tweak to the measure, to check whether the trend_rise field has one value, and if so then use that, otherwise use the default:

Value + Trend = 
SUMX ( 
    'Demo Fact Table',
    'Demo Fact Table'[Value] * 
        ( 1 +
            IF ( 
                ISFILTERED ( 'Item Chooser'[Item] ) &&
                CONTAINS ( 
                    'Item Chooser',
                    'Item Chooser'[Item],
                    'Demo Fact Table'[Item]
                ) && 
                HASONEVALUE ( 'Scenario - Trend'[Trend Rise] ),
                VALUES ( 'Scenario - Trend'[Trend Rise] ),
                'Demo Fact Table'[trend_default]
            )
        )
    )

Now you can use slicers on all columns of your Scenario table.

See https://pwrbi.com/so_55332313/ for worked example file.

Olly
  • 7,749
  • 1
  • 19
  • 38
  • Thanks, it was a lot easier than the solutions I were trying to implement..If you find the time, could you have a look at this extention of the solution? https://stackoverflow.com/questions/55343387/scenario-what-if-analysis-select-multiple-try-multiple-kpi-values-based-on-sl – Anton Mar 25 '19 at 17:26
  • Or alternatively, how would I extend this solution to look year on year values, i.e. in my main table I would plot sales per year, and then I could select "positive trends" & "low" (or something) and it would fetch for each year the said value, and in my main table apply different trend for each year. – Anton Mar 26 '19 at 07:21
  • Create a measure for each trend value? We're straying off topic of answering programming questions, and moving into report design, now... – Olly Mar 26 '19 at 07:52
  • Sorry I'm not following, I would create a seperate measure for each year and hard code the filter in there? – Anton Mar 26 '19 at 08:33