Top 3 of a category by Measure and other category

[H3nning]

Hi,

maybe someone can halp me with a ranking function please.

Im trying to build a ranking based on a fact table:

Agent	Supplier	Success
A	X	TRUE
A	X	FALSE
A	Y	FALSE
A	Y	FALSE
B	X	TRUE
B	X	FALSE
B	Y	TRUE
B	Y	TRUE
B	Y	FALSE

I built a measure [RateOfSuccess] for the percentage of success. For example Agent A hat a success rate of 50% with supplier X and 0% with Y and 25% overall. Please use that measure in a possible solution, because it stands for a much more complex measure in reality.

Now I want to give a Top N List auf Suppliers for every Agent. The result for my little example would be:

Agent A	Agent B
X	Y
Y	X

Or if I only want Top 1:

Agent A	Agent B
X	Y

Suggestions how to accomplish?

Thanks so much in advance 🙂

[v-shex-msft]

HI @H3nning,

You can try to use the following measure formula to get the top supplier based on current agent group:

formula = 
VAR currAgent =
    SELECTEDVALUE ( 'Table'[Agent] )
VAR summary =
    SUMMARIZE (
        ALLSELECTED ( 'Table' ),
        [Agent],
        [Supplier],
        "c_Supplier", CALCULATE ( COUNT ( 'Table'[Supplier] ), 'Table'[Success] = TRUE () )
    )
RETURN
    CONCATENATEX (
        TOPN ( 1, FILTER ( summary, [Agent] = currAgent ), [c_Supplier] ),
        [Supplier],
        ","
    )

Notice: the sample table success field stored the bool type values, if your table are used text type, please change the TRUE function in expression to text value to get correct results.

Regards,

Xiaoxin Sheng

[H3nning]

Hi, thank you for your reply. Can you provide the code with using a measure instead of building the measure in the code? In my example the rate of success stands for a much more complicated measure. Lats call it [RateOFSuccess] and it is already rthere and is to be used. How can i use that measure in your formula?

[v-shex-msft · Accepted solution]

Hi @H3nning,

You can try modify the collection part to add a filter and invoke the 'success' expression results as condition:

formula =
VAR currAgent =
    SELECTEDVALUE ( 'Table'[Agent] )
VAR summary =
    SUMMARIZE (
        ALLSELECTED ( 'Table' ),
        [Agent],
        [Supplier],
        "c_Supplier",
            CALCULATE (
                COUNT ( 'Table'[Supplier] ),
                FILTER ( 'Table', [Success] = "True" )
            )
    )
RETURN
    CONCATENATEX (
        TOPN ( 1, FILTER ( summary, [Agent] = currAgent ), [c_Supplier] ),
        [Supplier],
        ","
    )

If the above formula also not help, can you please share a pbix file or some dummy data that keep the raw data structure, Dax formulas to upload and paste the share link here? It should help us clarify your scenario and test to coding formula. (notice: please not attach any sensitive data in it)

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng