Power BI: How to scenario analysis, where the selector "looks up" the select value from slicer and gets values from that row

0
votes

I've followed multiple tutorials on scenario analysis and what if analysis in power BI. these examples are quite clear to me and I somewhat understand how they work. For example this (https://community.powerbi.com/t5/Community-Blog/Scenario-Analysis-What-Ifs-Tips-amp-Techniques-For-Power-BI/ba-p/559653).

In this example they create a table that contains values for different scenarios

5% 10% 15% 20%

etc. and the user of the report can select these. But what if instead of direct values I have categorical values, such as "Low, medium, high, very high" like:

category - trend rise
low         5%
medium      10%
high        15%
very high   20%

On my other table I have:

Value - Item - trend_default
10       A      0.3
15       A      0.2
20       B      0.4
25       B      0.15
35       C      0.10
30       C      0.05

Now with a slicer I want to first select none, one or all of Items, after which I want to select which trend rise I want to apply on selected items(low, medium, high or very high). If I select only item A then Item B and C use their trend_default value in the calculation.

1
powerbilookupanalysisscenarios

1 Answers

1
votes

ORIGINAL ANSWER

You can create your disconnected table with more than one column, like your example.

Then create a measure, based on the selected value of that table. Something like:

Value + Trend Rise = 
    VAR Multiplier = 
        1 + IF ( 
            HASONEVALUE ( 'Scenario - Trend'[Category] ),
            VALUES ( 'Scenario - Trend'[Trend Rise] ),
            0
        )
    RETURN
        SUM ( 'Demo Fact Table'[Value] ) * Multiplier

See https://pwrbi.com/so_55281950/ for worked example PBIX file

UPDATE, BASED ON EDITED QUESTION:

First, create a disconnected dimension table to choose (slice) your Items:

Item Chooser = 
    SUMMARIZECOLUMNS ( 
        'Demo Fact Table'[Item]
    )

Now you can use measure:

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

It should be straightforward to modify this if you wanted to return a default trend value per item, from a related item table.

Updated example file: https://pwrbi.com/so_55281950-2/