Top 10 / Other

Hi Anonymous

I have edited your file - uploaded here:

PBIX link

I slightly changed the measure for top sales person per group, but the logic is basically the same:

Sales for Top Sales Person per Group = 
CALCULATE (
    [Sales Amount],
    GENERATE (
        VALUES ( HighestSalesGroupWise[Group] ),
        TOPN (
            1,
            CALCULATETABLE (
                VALUES ( HighestSalesGroupWise[Sales Person] ),
                ALL ( HighestSalesGroupWise[Sales Person] )
            ),
            [Sales Amount]
        )
    ),
    VALUES ( HighestSalesGroupWise[Sales Person] )
)

For your Top 10 chart, I wasn't sure which top 10 you wanted to show. Can you give an example of expected outputs for that chart based on your data?

Owen

Hspuybroek

You can write the "Sales Amount Other" measure in my model without using EXCEPT, but ALLEXCEPT does something different so isn't appropriate here.

I'm not sure whether your version of Tabular allows variables or not, so here are two versions of the "Sales Amount Other" measure with and without variables:

Sales Amount Other NO EXCEPT WITH VARIABLES = 
VAR TopCustomers =
    TOPN ( [TopN Selection], ALL ( Sales[Customer] ), [Sales Amount] )
RETURN
    CALCULATE (
        [Sales Amount],
        KEEPFILTERS (
            FILTER (
                ALL ( Sales[Customer] ),
                NOT ( CONTAINS ( TopCustomers, Sales[Customer], Sales[Customer] ) )
            )
        )
    )

Sales Amount Other NO EXCEPT WITHOUT VARIABLES = 
CALCULATE (
    [Sales Amount],
    KEEPFILTERS (
        FILTER (
            ALL ( Sales[Customer] ),
            NOT (
                CONTAINS (
                    TOPN ( [TopN Selection], ALL ( Sales[Customer] ), [Sales Amount] ),
                    Sales[Customer], Sales[Customer]
                )
            )
        )
    )
)

Regards,

Owen

Anonymous

The CALCULATETABLE section in red is intended to return the list of all Sales Persons that have existing rows in the currently iterated Group (i.e. VALUES ( HighestSalesGroupWise[Sales Person] ) ) , but ignoring any Sales Person filters (which is why ALL is used), so you're right it is carrying out context transition to get the Sales Persons in the context of the currently iterated Group.

An alternative for the red section might be ALL ( HighestSalesGroupWise[Sales Person] ) but this would include all Sales Persons even with blank values for [Sales Amount]. That might cause blanks to be ranked higher than negatives. But it would be fine if all your sales values are nonnegative.

Measure for reference:

Sales for Top Sales Person per Group =
CALCULATE (
    [Sales Amount],
    GENERATE (
        VALUES ( HighestSalesGroupWise[Group] ),
        TOPN (
            1,
            CALCULATETABLE (
                VALUES ( HighestSalesGroupWise[Sales Person] ),
                ALL ( HighestSalesGroupWise[Sales Person] )
            ),
            [Sales Amount]
        )
    ),
    VALUES ( HighestSalesGroupWise[Sales Person] )
)

Owen :)

Hi heroncarlos

I was just looking back at this thread and notice I missed your message when I was on vacation!

Your case is slightly different as you need the actual values + "Others" to be in the same dimension, so you need to create an extra table with this dimension.

I have seen a few blog posts on this topic - here is one:

https://www.oraylis.de/blog/show-top-n-and-rest-in-power-bi

Hi Matthias_13

I used the built-in Matrix visual when I first created this. However, the Matrix visual has been upgraded since then, and when you click on an "old" Matrix visual, you won't see any visual selected in the Visualizations pane. You can upgrade it to a new Matrix by clicking on the new Matrix icon.

With the new Matrix visual, you have more layout/formatting options and can produce basically the same layout as the old Matrix. For example, I set "Stepped layout" to Off to get a "tabular" layout (i.e. separate column per row header).

I have added a second tab to my sample file at the same link

https://www.dropbox.com/s/59cct4in6zqbxaj/Sales%20Top%20Other.pbix?dl=0

I have updated the above file with an "Old Matrix" and "New Matrix" tab, so you can compare the two.

Owen :)

alexbalazsalex 

That is an interesting requirement 🙂

Just confirming, what would you like to display instead of blanks? Just the usual [Th Tn 2] measure value for that country?

(Just wanted to check this before writing the DAX)

If so, that shouldn't be too tricky. It would effectively change the behaviour so that the "top" countries are countries that are in the Top N in at least one of the displayed years.

Regards

Owen

Excellent Feedback...thx for your help!

Hi OwenAuger,

I found you might relly help me to get topN or Rankx value for my table. I see your example file but sill could not figureout how to achive top10 using multiple column in my case.

Can you help me to achive this?

If you want, I can share my file to you. I would really appricaite your help.

Best, Kulchandra

Hi Anonymous

It would help to share your file to get the names exactly right and ensure the measure works with your data model.

However, based on your example, the basic pattern I would use would be a measure that looks something like this:

Badge Counter Top =
CALCULATE (
    SUM ( Data[Badge counter] ),
    KEEPFILTERS (
        GENERATE (
            VALUES ( Data[Product Group] ),
            TOPN ( 1, ALL ( Data[Captured By] ), CALCULATE ( SUM ( Data[Badge counter] ) ) )
        )
    )
)

This measure filters Captured By down to the one with the top sum of Badge counter (for each Product Group). 

Please try that or post back with your model if needed.

Best regards,

Owen :)

Hi OwenAuger, Thank you for your feedback. I am not sure how to send you pbix file. My data link is : https://app.powerbi.com/groups/me/dashboards/b3b657db-34e8-41bc-a7fd-6a8902561186

I created the mesure you descriebed witout error. But filtering not working like I wnated. 

please seee what i got after mesure applied on fiter. 

Hi OwenAuger, I am shareing my file here. 

https://drive.google.com/file/d/0B6bBYAzZN3nFZ29yRlJxZVRjNXM/view?usp=sharing

please see the file and let me know how to get the solution. Thank you very much in adnvance.

Best, Kulchandra

Hi OwenAuger, Thank you for looking this in detail. Your solutions works for me and I tried to go in to more details. My table is also updated and now main table is "SalesTable".

Over all, I wanted to get columns for "AnyGroup-wise(see bullets bellow)", "Sales person" and "total sales of that person".

After that I will create a bar chart for each (country,job title,day,group)-wise.

1. Country-wise: I follow your steps and created another mesure "Highest Sales person CountryWise" and there is one coutnry which is repeating, I could not found the reason. Could you please check?
2. Job title-wise: I follow your steps and created another mesure "Highest Sales person Job title wise" and it looks great.
3. Day-wise: I didn't tried yet.
4. Group-wise: Group wise is the one you solved for me. and it looks perfect.

Can you help to make some type of slicers for selcting any Top 10/N values? or I can choose any of 4 bullets above to see my top 10 in selcted bullet?

I am attaching my file here: https://drive.google.com/file/d/0B6bBYAzZN3nFVVJNb1FUMmpWZHM/view?usp=sharing

Thank you in advance.

Kulchandra

Anonymous I have not had a chance to look at your follow up questions yet but will answer as soon as I can - someone else may get there first :)

Anonymous

Apologies for delay in responding - you may have figured this all out already!

1. The repeated country appears to be due to a tie for top person in that country. Does this matter? If you would prefer to eliminate ties, then you would need to use some other value to break them or arbitrarily take the first/last person.
2. Working fine
3. Apply the same pattern as the other measures and it should work.
4. Working fine.

Just checking what you want to happen with the TopN:

Taking Job title-wise top person for example, do you want to see the top N "top person per job title" by job title? So if N=3, you would see this?

Director - Ryan Reeve - 8

Radiologist - John Pace - 8

Manager - John Pace 7

Hi Owen

And thanks for the great example. I was able to use it when having data without any period. How would you modify it in case i wanted to have periods in columns? Now when adding periods, i'll get top10 for each period, not just top10 for the whole year for example.

BR,

Jani

Jani34

If you want the TopN to be determined over the whole year (or selected period), wherever TOPN(...) appears in the formula, replace it with

CALCULATE ( 
    TOPN ( ... ),
    ALLSELECTED ( Calendar )
)

assuming you have a Calendar table. Otherwise replace Calendar with the appropriate column or table.

OwenAuger

Hi Owen

Thanks for you quick reply. I tried your logic and it works when I don't have periods on the columns. Please see attached picture. I would just like the present top7 in this case on yearly level. So i would not want to show Cust4 in top customers, as it has been there only for one quarter.

BR;

Jani

Hi OwenAuger,

Thanks for providing the exact solution to the problem of many.

I am new to Power BI, implemented your solution and got the desired results except 1 thing.

The Rank 1 is showing as the sum of the entire column for total, attaching the screenshot. Could you please let me know what's the mistake I am making.

Many Thanks,

Anmol

Hi OwenAuger Thank you for sharing that template. I managed to implement TOP BOTTOM functionality from that solution.

I didnot understand how you managed to sort Matrix. I have created Top & Other table and 2 columns are Index and Top & Other. 

Data type of Index is Whole number, still my Matrix order doesn't work correctly. Appreciate if you can throw some light.

Matrix Order doesn't work

Jani34

Sorry - looks like I missed your post from Feb!

What logic were you wanting to apply? Only allow 'top' customers to include those present for the full year?

anmsharma

Hard to say - can you provide more details of your data model- ideally upload a sanitised pbix?

digant

I'm not sure exactly what's going wrong in your case.

I had a play around with the matrix in my pbix file, and I seem to be able to independently sort "Top & Other" and "Rank" columns within the matrix, just by clicking the column headers. If your Top/Other order is wrong, you should be able to correct it just by clicking the column heading. Does that work?

This is excellent!

I have managed to integrate into a top 20 / other report but have one question.

How would i adapt so the ranking shows only for the Total row?

I have columns in the report that dont need ranking and this ranks each column seperately 

Thanks for awesome Post for finding out TOP 10. I have a question on top of what you have described.

If we have a Quarter column in the table and we need to display value in matrix with Quarter at column level such that whatever will be top 10 based on current quarter. Then, Same top 10 customer should be dispalyed for each quarter. Also, region filter sould apply as it is being applied in your example.

Thanks in advance.