TOP 20 percentage

[leolam1234]

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

[Anonymous]

@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]

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

[Ashish_Mathur · Accepted solution]

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.

[leolam1234]

it works, thanks a lot

[Ashish_Mathur]

You are welcome.

[TeigeGao]

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]

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