Forum Discussion

badger123's avatar
badger123
Resolver I
7 years ago
Solved

Rank, count rank and average

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 t...
Need Help
  • AlB's avatar
    AlB
    7 years ago

    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'.  

     

AlB's avatar
AlB
Community Champion
7 years ago

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's avatar
AlB
Community Champion
7 years ago

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's avatar
    badger123
    Resolver I
    7 years ago

    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!!