TOPN percentage of total

[Anonymous]

We have a table with following columns.

Date - Amount - Postcode

For each day we have a number postcodes with an amount of things we count per postcode.

Then we have another table with areas. Area can consist of one or many postcodes.

I have a column chart that shows TOP 20 (per amount) areas. I'd like to show on a card a percentage that would be a summed amount of that Top 20 compared to sum of all. 

User can drill down on the column chart on different levels (month, week etc) and I need the percentage to act accordingly (to show top 20 of say a month compared to sum of all on that month).

Also I'd like to filter out rows with blank postcode.

Any tips?

Thanks!

Edit: I'm using DirectQuery.

Edit 2: This is what I have now.

Top20MunicipalitiesAmount = CALCULATE( SUM(PostcodeAmounts[Amount]); 
FILTER(
TOPN(20;
GROUPBY(PostcodeAmounts;PostcodeAreas[Municipality];"Summed";SUMX(CURRENTGROUP();PostcodeAmounts[Amount]));[Summed]);
PostcodeAreas[Municipality] <> BLANK())
)

It gives me ALMOST correct results for top 20 sum, but seems to be off by a couple of percent some times compared to the values I get when using the Power BI Desktop interface and dragging same filters to a card. I have no idea what that could be about.

[Anonymous · Accepted solution]

I think I got it. This is what I use now to calculate the top 20 sum.

Top20MunicipalitiesAmount = CALCULATE( SUM(PostcodeAmounts[Amount]); 
TOPN(20;
    GROUPBY(FILTER(PostcodeAmounts;PostcodeAmounts[PostcodeId] <> BLANK());PostcodeAreas[Municipality];"Summed";SUMX(CURRENTGROUP();PostcodeAmounts[Amount]));
    [Summed]);
PostcodeAreas[Municipality] <> BLANK()
)

[makcim392]

why are you using semicolons? aren't those supposed to be comas?

[Anonymous]

I think it is a regional thing.

[v-cherch-msft]

Hi @Anonymous

It seems you've solved the issue, right? If it is, please mark your answer as solution. If not, please share some data sample. You can upload it to OneDrive or Dropbox and post the link here. Below is the similar post for your reference.

https://community.powerbi.com/t5/Desktop/calculating-percentage-of-grand-total-of-a-total-per-customer/td-p/307978

Regards,

Cherie