TOP 10 - Pie Chart

[A_Barny]

Hi everyone,

I am trying to do a pie chart with a TOP 10 resulats. But I want the last top ( number 10) is "Others".

TOP 1 - TOP 2 - TOP 3 - TOP 4 - TOP 5 - TOP 6 - TOP 7 - TOP 8- TOP 9 - Others

Do I need to create a new measure  ? 

Thanks for your help.

Alex

[v-frfei-msft]

Hi @A_Barny,

I make a test using my sample table and create two columns as below:

rank = RANKX(ALL(Table1[Column1]),CALCULATE(SUM(Table1[amount]),ALLEXCEPT(Table1,Table1[Column1])))

 Top10 = IF(Table1[rank]<10,Table1[Column1],"Top Others")

Then we can draw a chart like this, for more details, please check the pbix as attached. 

https://www.dropbox.com/s/ogcj12wddjb9056/TOP%2010%20-%20Pie%20Chart2.pbix?dl=0

If you still have questions about DAX, please share sample data of your table.

Regards,

Frank

[A_Barny]

Hi @v-frfei-msft;

Thanks for your answer. 

I have a problem with your two requests because I use 2 things.

The first, a column like you for "Table1[Column1]" , for me use for Customers. 

The second, I don't use a column like you use "Table1[amount]", I need to use a mesure create before. This mesure calculate the YTD quantity. This mesure, is use in an other Table

The main problem is I have only one values per each Customers select. So for this syntaxe DAX, it doesn't work because I have 1 value.

Error send by Desktop : Too few arguments have been passed to the ALLEXCEPT function. The minimum number of arguments for the function is 2.

Typically, I have this table :

Table A - Customers	Table B -   YTD Quantity
Customer 1	150
Customer 2	3000
Customer 3	40
Customer 4	538
Customer 5	42
Customer 6	500000

But If I select only  "Table B -   YTD Quantity", you will just have the YTD Quantity but for all customers.

Thanks you for your answers,

Alex

[v-frfei-msft · Accepted solution]

Hi @A_Barny,

I created two new tables as your description. And take the following steps to meet your requirement.
If your tables are the same as the ones in pbix attached, you can have a try.

1. Create connection between TableA and tableB based on the date columns. Create the YTD measure.

YTD = TOTALYTD(SUM(TableB[Quantity]),TableB[Date])

2. Create two calculated columns in TableA.

Rank = RANKX(ALL(TableA[Customers]),CALCULATE(SUM(TableB[Quantity]),ALLEXCEPT(TableA,TableA[Customers])))

top 10 = IF(TableA[Rank]<10, TableA[Customers], "Top Others")

Then we can get the result as below.

For more details, please check the pbix as attached.

https://www.dropbox.com/s/bnaavh9mw9ck5qm/TOP%2010%20-%20Pie%20Chart-re.pbix?dl=0

If you still have questions, kindly share your PBIX file to me.

Regards,

Frank

[A_Barny]

It' s work !

Thanks a lot,

Alex