Dax 转置基于 Top N 的表 - Power BI

问题描述

在 POWER BI DAX 中寻求帮助 - 我有一个 DAX 创建的表，该表采用名称并将各种结果及其可能性转换为以下格式:

Looking for some assistance in POWER BI DAX - I have a DAX created table that takes a Name and transposes various outcomes with their likelihood into the following format:

目前，我有一个措施来确定前 3 个不同的输出，其形式如下:

Currently, I have a measure in order to identify the top 3 varying outputs which takes form like:

Most Likely =
LOOKUPVALUE(
    'Test'[Result],
    'Test'[Expected Percentage],
    MINX(
        TOPN(
            1,
            'Test',
            'Test'[Expected Percentage],
            desc),
        'Test'[Expected Percentage])
    )

我使用它在我的仪表板中显示，根据预期输出的最高百分比显示最可能的结果.

I use this for display in my dashboard to show the most likely Result based on the top percentage expected output.

但是，现在我想将这些数据转置为以下静态表格格式:

However, now I want to transpose this data into the following static table format:

我遇到的问题是 DAX 表添加语句没有按名称拆分，因此我正在检索整个表的最高预期百分比，并且它没有按名称拆分 - 这是我目前正在使用的:

The problem I am having is the DAX table add statement is not splitting by the Name so therefore I am retrieving the top Expected percentage for the whole table and it is not splitting per name - this is currently what I am working with:

Outcomes Summary = (
SELECTCOLUMNS('Test', "Most Likely",
LOOKUPVALUE(
    'Test'[Result],
    'Test'[Expected Percentage],
    MINX(
        TOPN(
            1,
            'Test',
            'Test'[Expected Percentage]&'Score Outcomes'[Name],
            desc),
        'Score Outcomes'[Expected Percentage])
    ), "Name", 'Test'[Name], "Result", 'Test'[Expected Percentage]
)
)

任何帮助将不胜感激！

推荐答案

如果你先在表中添加一个排名列，这会变得容易一些.

This becomes a bit easier if you first add a ranking column to the table.

Summary =
VAR AddRank =
    ADDCOLUMNS (
        Test,
        "@Rank",
            RANKX (
                FILTER ( Test, Test[Name] = EARLIER ( Test[Name] ) ),
                Test[Expected Percentage]
            )
    )
RETURN
    SUMMARIZE (
        AddRank,
        Test[Name],
        "Most Likely",
            MAXX (
                FILTER ( AddRank, [@Rank] = 1 && Test[Name] = EARLIER ( Test[Name] ) ),
                Test[Result]
            ),
        "2nd Most Likely",
            MAXX (
                FILTER ( AddRank, [@Rank] = 2 && Test[Name] = EARLIER ( Test[Name] ) ),
                Test[Result]
            ),
        "3rd Most Likely",
            MAXX (
                FILTER ( AddRank, [@Rank] = 3 && Test[Name] = EARLIER ( Test[Name] ) ),
                Test[Result]
            )
    )

这篇关于Dax 转置基于 Top N 的表 - Power BI的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！