Calculate number of customers generating X% of total revenue

[Anonymous]

Hi all,

Can you please help with this calculation?

I have a generic sales transaction table, with fields CustomerID, YYYYMM, SalesRevenue

I need to get the total number of customers who generated 90% and 95% of total revenue during the month (ex. 202203)

Can anyone help please?

Thanks

Jon

[TomMartens]

Hey @Anonymous ,

without more information about the data model it's difficult to help you with your question.

Create a pbix file that contains sample data but still reflects your data model (tables, relationships, calculated columns, and measures). Upload the pbix to onedrive or dropbox and share the link. If you are using Excel to create the sample data instead of the manual input method, share the xlsx as well.

Describe the expected result based on the sample data.

Next to that, you have to be aware that there is a tie, meaning the top 3 clients create 90%, but the value of the 4th client is exactly the same as the 3rd client, what is the expected result?

Providing a solution without sample data and a proper data model is difficult maybe not possible due to the concept of AutoExist (Understanding DAX Auto-Exist - SQLBI).

Nevertheless, the DAX function to use will be RANKX in combination with a measure that accumulates the value according to the rank

This sample file contains a measure "ms Share Amount **bleep**" that demonstrates how a cumulated value can be calculated base on a rank.

Regards,

Tom

[SpartaBI · Accepted solution]

@Anonymous this is the measure for 90 (make sure you also have the primary measure in the first line in your model):

MEASURE 'Table'[SalesRevenueMeasure] = SUM(SalesRevenue)

MEASURE 'Table'[Customers_in_Percentile_90] = 
VAR _tbl = 
	ADDCOLUMNS(
		VALUES('Table'[CustomerID]),
		"@Sales", [SalesRevenueMeasure]
	)
VAR _pct90 = PERCENTILEX.INC(_tbl,[@Sales],0.90)
VAR _result = 
	COUNTROWS(
		FILTER(
			_tbl,
			[@Sales] < _pct90
		)
	)
RETURN
	_result

For 95, just change it in the code from 90 to 95

In case it answered your question please mark this as a solution for community visibility. Appreciate Your Kudos 🙂

[amitchandak]

@Anonymous , I Think you need percentile

example

Top 80/20 , percent /percentile
https://blog.enterprisedna.co/implementing-80-20-logic-in-your-power-bi-analysis/
https://forum.enterprisedna.co/t/testing-the-pareto-principle-80-20-rule-in-power-bi-w-dax/459
https://finance-bi.com/power-bi-pareto-analysis/
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-the-sum-of-the-top-80/td-p/763156