Dax to Transpose a table based on Top N - Power BI

Question

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:

Dax Created Table Look

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:

Desired Table format

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

Any assistance would be greatly appreciated!

Alexis Olson Alexis Olson · Accepted Answer · 2021-07-07T16:55:02

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 to Transpose a table based on Top N - Power BI

1 Answers