topic Re: Calculate percentage of turnover of top N products on total turnover in DAX Commands and Tips

Calculate percentage of turnover of top N products on total turnover

Dukeable — Tue, 16 Nov 2021 15:40:17 GMT

Hi there!

I'm sure the issue is probably simple but I'm struggling with the DAX.
I have a dashboard that gives me my top 10 items on a certain category (audio as an example). These make a turnover of 8.000.000€. The total turnover of audio is 19.000.000€. Now I want to divide my turnover of my top 10 products from the category by the total turnover of the audio category in order to be able to put them in relation to each other. I am sure that I have to work with DIVIDE, TOPN, SUMX and or CALCULATE - but unfortunately I can't solve it yet. Can anyone help me?

Best regards!

Steffen

Re: Calculate percentage of turnover of top N products on total turnover

VahidDM — Tue, 16 Nov 2021 22:09:09 GMT

Hi Dukeable

Can you post sample data as text and expected output?
Not enough information to go on;

please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

Re: Calculate percentage of turnover of top N products on total turnover

Dukeable — Wed, 17 Nov 2021 10:49:46 GMT

Hi VahidDM for sure. Thank you for your information - I will keep that in mind for further postings.

One Visual/Table with the Top 4 Styles for category audio as follow:

Tops Styles for Category Audio	turnover
1. Style	5.000.000
2. Style	1.000.000
3. Style	750.000
4. Style	250.000
5. Style	100.000
Total	7.100.000

So the filter is category: audio and Top 5 Styles listed DESC in addtion to the turnover.

In a second visual/table i have the whole turnover for category audio:

Category	Turnover
Audio	19.000.000

The filter for that table is only category: audio.

I also have an table with all stlyes where i can get the top 5 from

Style	Catgory	turnover
1	Audio	30.000
2	Gaming	1.500
etc.	etc.	etc.

So what I want as an output to see is what is the percentage of the top style on the whole turnover for category audio. So the result should be: The turnover of the top 5 stlyes in the catgory audio has an percentage of 37,37% on the whole turnover of category audio. So I can show if the impact of the top styles is high/low regarding the whole turnover.

I hope I made it more clearer now 😕

Best

Steffen

Re: Calculate percentage of turnover of top N products on total turnover

VahidDM — Wed, 17 Nov 2021 22:02:04 GMT

Hi Dukeable

It'd be greate if you share a sample of your data table, but if I got your point correctly try this measure:

Measure = Var _A = filter(ADDCOLUMNS('Table',"Rank",RANKX(filter(all('Table'),[Category ]="Audio"),[Turnover],,DESC,Dense)),[Rank]<=5) Var _B = CALCULATE(SUM('Table'[Turnover]),filter(all('Table'),'Table'[Category ]=max('Table'[Category ]))) return SUMX(_A,[Turnover])/_B

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

Re: Calculate percentage of turnover of top N products on total turnover

Dukeable — Thu, 18 Nov 2021 11:09:28 GMT

Hi VahidDM thank you for your help so far. Due the fact I'm using an OLAP-Datebase I'm not able to have a deeper view into the tables.

But I've tried to copy the both relevant Tabels/Measures:

I did the selection of category by the filter function directly in power bi.

Hope it helps! Sorry for my incomplete posting 😕 I'm pretty new...

Best

Steffen