Forum Discussion

A_Barny's avatar
A_Barny
Frequent Visitor
8 years ago
Solved

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 - Oth...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    8 years ago

    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

v-frfei-msft's avatar
v-frfei-msft
Community Support
8 years ago

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's avatar
A_Barny
Frequent Visitor
8 years ago

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's avatar
    v-frfei-msft
    Community Support
    8 years ago

    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's avatar
      A_Barny
      Frequent Visitor
      8 years ago

      It' s work !

       

      Thanks a lot,

      Alex