Rank, count rank and average

[badger123]

I have a table that looks something like this:

Country	Category	Term	Brand	Value
UK	category a	category a term one brand one	brand one	20
UK	category a	category a term two brand two	brand two	10
UK	category a	category a term three	N/A	30
UK	category b	category b term one brand one	brand one	20
UK	category b	category b term two brand three	brand three	100
UK	category c	category c term one brand one	brand one	10
UK	category c	category c term two brand two	brand two	20
UK	category c	category c term three brand three	brand three	30
UK	category c	category c term four brand one	brand one	30

And I'm trying to figure out, for each brand in each country, how many categories it ranks within the top 2 and how many it ranks as the top 1, based on the sum of values for a given category. So in this case, it would be:

Country	Brand	Ranks in top 2 for X categories	Ranks in top 1 for X categories
UK	brand one	3	2
UK	brand two	1	0
UK	brand three	2	1

What I then want to do is calculate the average of all brands ranking in the top 2 (so 3+1+2 / 3 = 2) and the average of all brands ranking as the top 1 (2+0+1 / 3 = 1).

Please help! I just cannot figure this out. 

[AlB · Accepted solution]

Hi @badger123 

Try this:

1. Place Table1[Country] and Table1[Brand] in a table visual

2. Create these two measures and place them in the table visual:

Measure1 = 
SUMX (
    ADDCOLUMNS (
        DISTINCT ( Table1[Category] );
        "Ranks_in_Top1"; 1
            * (
                COUNTROWS (
                    FILTER (
                        TOPN (
                            1;
                            CALCULATETABLE (
                                SUMMARIZE ( Table1; Table1[Country]; Table1[Category]; Table1[Brand] );
                                ALL ( Table1[Brand] )
                            );
                            CALCULATE ( SUM ( Table1[Value] ) ); DESC
                        );
                        Table1[Brand] = SELECTEDVALUE ( Table1[Brand] )
                    )
                ) > 0
            )
    );
    [Ranks_in_Top1]
)

Measure2 =
SUMX (
    ADDCOLUMNS (
        DISTINCT ( Table1[Category] );
        "Ranks_in_Top2"; 1
            * (
                COUNTROWS (
                    FILTER (
                        TOPN (
                            2;
                            CALCULATETABLE (
                                SUMMARIZE ( Table1; Table1[Country]; Table1[Category]; Table1[Brand] );
                                ALL ( Table1[Brand] )
                            );
                            CALCULATE ( SUM ( Table1[Value] ) ); DESC
                        );
                        Table1[Brand] = SELECTEDVALUE ( Table1[Brand] )
                    )
                ) > 0
            )
    );
    [Ranks_in_Top2]
)

Take into account that this will show "N/A" as brand (you haven't in your example) and it sums all items of a brand. For instance, under category C, there are two entries for 'brand one'. The code above considers the 30+10 as value for 'brand one'.  

[badger123]

Thanks @AlB  ! That solves my first problem 🙂

Do you know how to solve the second part?

What I then want to do is calculate the average of all brands ranking in the top 2 (so 3+1+2 / 3 = 2) and the average of all brands ranking as the top 1 (2+0+1 / 3 = 1).

[AlB]

You can do this:

Measure2_Total =
AVERAGEX (
    ADDCOLUMNS (
        SUMMARIZE ( Table1; Table1[Country]; Table1[Brand] );
        "Aux"; CALCULATE (
            SUMX (
                ADDCOLUMNS (
                    DISTINCT ( Table1[Category] );
                    "Ranks_in_Top2"; 1
                        * (
                            COUNTROWS (
                                FILTER (
                                    TOPN (
                                        2;
                                        CALCULATETABLE (
                                            SUMMARIZE ( Table1; Table1[Country]; Table1[Category]; Table1[Brand] );
                                            ALL ( Table1[Brand] )
                                        );
                                        CALCULATE ( SUM ( Table1[Value] ) ); DESC
                                    );
                                    Table1[Brand] = SELECTEDVALUE ( Table1[Brand] )
                                )
                            ) > 0
                        )
                );
                [Ranks_in_Top2]
            )
        )
    );
    [Aux]
)

and change 2 for 1 in the first argument of the TOPN for the other measure 

[AlB]

or if you want it a bit more readable, reutilize the measured we created initially:

Measure2_Total_V2 =
AVERAGEX (
    ADDCOLUMNS (
        SUMMARIZE ( Table1; Table1[Country]; Table1[Brand] );
        "Aux"; [Measure2]
    );
    [Aux]
)

and change [Measure2] for [Measure1] in the code for the other measure

[badger123]

Amazing, thanks @AlB . One more question, if I have two slicers on the page (Country and Brand), and I wanted to calculate the average as below for all Brands (ignoring the Brand slicer) in the Country (selected in slicer), how can I do this? 

I can't figure out where to put ALL() to make it work!!