Help Making a % of Total Column with only Top 30 Items

[Anonymous]

HI - 

I am trying to create a % of Total column for (YTD sales figures for ONLY Blue in Top 30)/(YTD Sales for Blue and Red for all rankings).

The Blue filter is a pagewide filter currently.

I am having trouble finding a way to divide by the full total amount for both red and blue and not only top 30.

Does anyone have any suggestions?  

Thanks, 

H

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a measure as below.

% Total Sales = 
DIVIDE(
    SUM('Table'[Sales]),
    CALCULATE(
        SUM('Table'[Sales]),
        ALL('Table')
    )
)

Result：

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@Anonymous , then a measure like this should work

% of total = divide([sales],[all City]) //all City =CALCULATE([Sales],all(Geography))

[amitchandak]

@Anonymous , you can use topN for that

example

Top 30 City Rank = CALCULATE([Sales],TOPN(30,all(Geography[City]),[Sales],DESC),VALUES(Geography[City Id]))

all City  =CALCULATE([Sales],all(Geography))

% of Top 30 = divide([Top 30 City Rank],[all City])

https://youtu.be/QIVEFp-QiOk

[Anonymous]

Thanks for the suggestions! I think i was unclear with my question -- I have added a screen shot below to try to illustrate.  I need to get the % for each line, not just the overall top 30.  I have used top 5 in the example below