Top 10, "Others", and Grand Total

[rhayami]

Hi, 

I have a table that I was able to use rank to display top 10.  I need the remaining in "Others" after Top 10 total, then "Grand Total", which is all of them at the bottom in one visualization.  Please let me know the easiest way to do this.  I've tried a couple of steps I found from searching but did not seem to work.  2nd screenshot is the desired outcome I've created in Excel.  Thanks!  

I tried this complex solution offered here https://medium.com/microsoft-power-bi/advanced-topn-in-power-bi-deb650b508c9 but it just would not show "Others" for me. 

Note:  Sales $ displayed in the column is a calculated measure, so some of the solutions I've found wouldn't work. It is the value the top 10 needs to rank based on. 

Sales Amount corrected =

SUMX (

    SUMMARIZE('DATA_Product Sales','DATA_Product Sales'[Entry No_], 'DATA_Product Sales'[Sales Amount (Actual)]),'DATA_Product Sales'[Sales Amount (Actual)])

[v-hashadapu]

Hi @rhayami , Thanks for the update—great to hear the issue is now resolved. Your detailed explanation is valuable. To help others with similar queries, please mark your response as "Accept as Solution".

[v-hashadapu]

Hi @rhayami,  We appreciate the follow-up and are pleased to know the problem has been resolved. Thank you for providing the details here. Please consider marking your response as "Accept as Solution" so others can benefit from it as well.

[rhayami · Accepted solution]

So for anyone else who's searching for the solution on this, I followed this video step by step and got me most of what I needed.  Granted they are using a bar chart, but you can stop when he completes the table.  The only other thing I need to do is add a grand total that sums top N and Others.. 

https://www.youtube.com/watch?v=yGFcCbXn_g0

[v-hashadapu]

Hi @rhayami , Thanks for the update. We are happy to hear that you have resolved the issue. Thanks for sharing the details here. Please mark your insights 'Accept as solution' to help others with similar problems find it easily. 
Thank you.

[gmsamborn]

Hi @rhayami 

You might find this interesting.

https://www.sqlbi.com/articles/filtering-the-top-products-alongside-the-other-products-in-power-bi/

[rhayami]

Thank you for this.  I think it is a similar method as https://medium.com/microsoft-power-bi/advanced-topn-in-power-bi-deb650b508c9   I wish I could make this work.  

[sevenhills]

In one of the requirements, I did this, which is close to what you are doing 

DAX is from my old code, please tweak to your needs!

There are some disadvantages as the data gets created only during refresh. But users are ok as we refresh every day this report! Also, we are not given access to the power query and has to be DAX. 

concept:
1. Create a DAX table with all the top 10 

2. Add a row for top 10 total

3. Add a row for others total

4. Add a row for Grand total

 TopN Table = 
VAR TopNValue =  10 

-- Base summary with calculations
VAR Summary =
    ADDCOLUMNS(
        SUMMARIZE(
            Sales,
            Product[Category],
            Reseller[Business Type]
        ),
        "TotalSales", CALCULATE(SUM(Sales[Sales Amount])),
        "YTDSales", CALCULATE([YTD Sales]),
        "PYYTDSales", CALCULATE([PY YTD Sales]),
        "PctChange", 
            DIVIDE(
                CALCULATE([YTD Sales]) - CALCULATE([PY YTD Sales]), 
                CALCULATE([PY YTD Sales])
            )
    )

-- Rank within the summarized context
VAR Ranked =
    ADDCOLUMNS(
        Summary,
        "Rank", RANKX(Summary, [TotalSales], , DESC, DENSE) 
    )

VAR TopRows = 
    FILTER(Ranked, [Rank] <= TopNValue)

-- Totals for Top N
VAR TopTotalRow =
    ROW( 
        "Category", "Top " & TopNValue & " Total",
        "Business Type", "",
        "TotalSales", SUMX(TopRows, [TotalSales]),
        "YTDSales", SUMX(TopRows, [YTDSales]),
        "PYYTDSales", SUMX(TopRows, [PYYTDSales]),
        "PctChange", DIVIDE(SUMX(TopRows, [YTDSales]) - SUMX(TopRows, [PYYTDSales]), SUMX(TopRows, [PYYTDSales])),
        "Rank", 22
    )

-- Others row
VAR OthersRow =
    ROW(
        "Category", "Others",
        "Business Type", "",
        "TotalSales", CALCULATE(SUM(Sales[Sales Amount])) - SUMX(TopRows, [TotalSales]),
        "YTDSales", CALCULATE([YTD Sales]) - SUMX(TopRows, [YTDSales]),
        "PYYTDSales", CALCULATE([PY YTD Sales]) - SUMX(TopRows, [PYYTDSales]),
        "PctChange", 
            VAR YTD_Others = CALCULATE([YTD Sales]) - SUMX(TopRows, [YTDSales])
            VAR PY_Others = CALCULATE([PY YTD Sales]) - SUMX(TopRows, [PYYTDSales])
            RETURN DIVIDE(YTD_Others - PY_Others, PY_Others)
            ,
        "Rank", 32
    )

-- Grand total
VAR GrandTotalRow =
    ROW( 
        "Category", "Grand Total",
        "Business Type", "",
        "TotalSales", CALCULATE(SUM(Sales[Sales Amount])),
        "YTDSales", CALCULATE([YTD Sales]),
        "PYYTDSales", CALCULATE([PY YTD Sales]),
        "PctChange", DIVIDE([YTD Sales] - [PY YTD Sales], [PY YTD Sales]),
        "Rank", 42
    )

-- Spacer Row
VAR SpacerRow1 = ROW( "Category", "", "Business Type", "", "TotalSales", blank(), "YTDSales", blank(), "PYYTDSales", blank(), "PctChange", blank(), "Rank", 21  )
VAR SpacerRow2 = ROW( "Category", "", "Business Type", "", "TotalSales", blank(), "YTDSales", blank(), "PYYTDSales", blank(), "PctChange", blank(), "Rank", 31  )
VAR SpacerRow3 = ROW( "Category", "", "Business Type", "", "TotalSales", blank(), "YTDSales", blank(), "PYYTDSales", blank(), "PctChange", blank(), "Rank", 41  )

RETURN
    UNION(
        TopRows,      SpacerRow1,
        TopTotalRow,  --SpacerRow2,
        OthersRow,    --SpacerRow3,
        GrandTotalRow
     )

Format the table visual:

Final output:

Note: This is not perfect solution you are seeking but one of my old solution implemented! gives you some ideas to head start ... 

Hope it helps!

[rhayami]

Thank you for this.  I will try this but a question.  How did you format the Rank column?  I would not want to show 10.20 for "Others" for example. 

[sevenhills]

I know this isn't a perfect solution—as I mentioned—but the users were fine with it when I implemented it.

I've added an image showing how to apply the formatting. Here are the steps:

1. Select the table visual and go to the Format pane.

2. Under the Visual tab, expand Cell elements.

3. For the Series, choose "Rank" and then click on Font color.

4. Set the format style to Rules, apply it to Values only, and base the rule on Business Type, with Summarization set to Last.

5. Then configure the rules as shown in the image. (in previous post)

It's a bit of a process, but it worked well for my usecase!

When you export, the formatting is always visible. In power bi , it won't show—but the users are okay with that.

I also added a parameter to test different ranges, from Top 1 to Top 20, and built it accordingly.

[rhayami]

Thank you.  This is very helpful.  I actually have to add gross margin column, prior year's sales and prior year's gross margin columns with a year-over-year variance column.  It's a bit of a pointless analysis when some customers in top 10 didn't have any sales prior year, but that's what the management requested.  Would your solution work if you pull in more columns?  One solution I followed from the video totaly failed when I did this..