Power BI：如何进行方案分析，其中选择器“查找”从切片器中选择值并从该行获取值 [英] Power BI: How to scenario analysis, where the selector "looks up" the select value from slicer and gets values from that row

问题描述

我遵循了有关场景分析以及在Power BI中进行假设分析的多个教程。这些例子对我来说很清楚，我有点理解它们是如何工作的。例如，此（ https://community.powerbi.com/t5/Community-Blog/Scenario-Analysis-What-Ifs-Tips-amp-Techniques-For-Power-BI/ba-p/559653 ）。

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％

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

现在我要使用切片器，首先不选择任何一项或全部，然后选择要应用的趋势上升选择的项目（低，中，高或非常高）。如果我仅选择项目A，则项目B和C在计算中使用它们的trend_default值。

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.

推荐答案

原始答案

您可以创建多列断开连接的表格，例如您的示例。

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

请参见 https://pwrbi.com/so_55281950/ 用于PBIX文件示例

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

首先，创建一个断开连接的尺寸表以选择（切片）您的商品：

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

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

现在您可以使用度量：

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.

更新后的示例文件： https://pwrbi.com/so_55281950-2/

这篇关于Power BI：如何进行方案分析，其中选择器“查找”从切片器中选择值并从该行获取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！