Forum Discussion
TOP 10 - Pie Chart
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
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
4 Replies
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
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
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
