Forum Discussion

leolam1234's avatar
leolam1234
Regular Visitor
7 years ago
Solved

TOP 20 percentage

Hi everyone,

 

I would like to calculate the TOP 20 sale percentage of total sale by different store

 

My table have the column as below

Table Name: Top item sale

 

1. Store

2. item no

3. Net sale

 

How can I get a table like below:

 

Store A : 20%

Store B : 30%

Store C : 15%

 

Thank you very much

 

Leo Lam

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

    Try these measures

    Total sale = SUM(Data[Net sale])

    Total sale of Top 20 items = SUMX(TOPN(20,VALUES(Data[Item No]),[Total sale]),[Total sale])

    Total sale of Top 20 items (%) = [Total sale of Top 20 items]/[Total sale]

    Hope this helps.

7 Replies

  • TeigeGao's avatar
    TeigeGao
    Icon for Solution Sage rankSolution Sage
    7 years ago

    Hi leolam1234 ,

    According to your description, my understand is that you want to calculate the TOP 20 sale percentage of total sale by different store, the data is stored in table "Top item sale".

    Please use the following DAX query:

    Measure =
VAR a =
    CALCULATE (
        SUM ( 'Top item sale'[Net sale] ),
        TOPN ( 5, 'Top item sale', 'Top item sale'[Net sale], DESC )
    )
VAR b =
    CALCULATE ( SUM ( 'Top item sale'[Net sale] ) )
RETURN
    a / b

    Best Regards,

    Teige

    • leolam1234's avatar
      leolam1234
      Regular Visitor
      7 years ago

      Hi,

       

      Thanks for your reply

       

      Sorry that i have date field in the table, as i want to combine the sale with different date.

       

      For the method, it may show the Top sale with one date not summarized data

       

      Thanks

       

      Leo 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    Hi,

    Try these measures

    Total sale = SUM(Data[Net sale])

    Total sale of Top 20 items = SUMX(TOPN(20,VALUES(Data[Item No]),[Total sale]),[Total sale])

    Total sale of Top 20 items (%) = [Total sale of Top 20 items]/[Total sale]

    Hope this helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    leolam1234  a little bit hard to visualise without data but say if you had..

     

    Set up a slicer for store identifier (A,B,C)

     

    Then have a table

    Column 1: Store identifier (A,B or C)

    Column 2: Item Number

    Column 3: Net Sale

     

    Click on the table, go into visual level filters, click on Store Identifier,  Filter type drop down: "top N", put 20 into the show items, and move "Net Sale" field into the "By value".

     

    Then when you apply your slicer for each store identifier, you will be able to see the associated item number and corresponding net sale for the top 20 of sales by each store.

     

     

    • leolam1234's avatar
      leolam1234
      Regular Visitor
      7 years ago

      Hi Chris,

       

      Thank you for your help,

       

      Actually i would like to get the percentage of the TOP20 to the total sale.

       

      Do you have any idea?

       

      Thanks