how to calculate total customer count upto 80% sales

[vs_7]

Hi,

I am trying to get all the customers count  whose sales upto  80% of total sale.

After much research, I think the steps are: 

1. Create a Rank on sales

2. Do a cumulative sales

3. Look for 80% of those cumulative sales.

I prefer below link but its not working, its showing blank value

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b13a8ce5-c77d-43c2-8be4-7e300e02cdbc/count-...

please help i  am struggling from last 20 days.

[vs_7]

Hi @truptis ,

Appreciate for your response.

But it didnt worked for the required output.

[truptis]

@vs_7 Please see the codes provided by @Samarth_18 :
You need to create 3 new measures - 
50 % =
CALCULATE (
COUNT ( 'Table (4)'[customer code] ),
FILTER ( 'Table (4)', 'Table (4)'[cummulative] <= 50 )
)
80 % =
CALCULATE (
COUNT ( 'Table (4)'[customer code] ),
FILTER ( 'Table (4)', 'Table (4)'[cummulative] <= 80 )
)
100 % =
CALCULATE (
COUNT ( 'Table (4)'[customer code] ),
FILTER ( 'Table (4)', 'Table (4)'[cummulative] <= 100 )
)

[vs_7]

please help , i need show this calculation to my boss and i am stuck from last 20 days

[V-lianl-msft]

Hi @vs_7 ,

What output do you expect? Based on the sample data you provide, @Samarth_18  measure can work.

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

[vs_7]

hi @amitchandak @lbendlin @parry2k @Samarth_18 @truptis 

appreciate for your response but above data is shared is in exceel format.

I need all above calculations in DAX measures to get the required output.

i need to calculate cummulative sales, sales percentage, count upto 50, 80 percent from total sales contribution from customers from Turnover.

[amitchandak]

@vs_7 , have you already explored these

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

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[vs_7]

hi @amitchandak @lbendlin @parry2k @Samarth_18 @truptis 

i have used all dax which is suggested and checked in the forum but i didnt  get exact customer count.

i have three financial year of data , here i am sharing one financial year of data is in exceel calculation format.

we need Dax calculation measures from below Exceel data.

you can see below data :

upto 50% sales customer count is 3

upto 80% sales customer count is 6

upto 100% sales customer count is 9

please help.

Financial year	customer code	sales	total sales	% of sales	cummulative	50%	80%	100%
2019-20	30BLU12200	69,91,23,048.79	3,46,48,82,769.35	20.17739	20.17739402	50	80	100
2019-20	ME01A10976	52,57,00,902.09	3,46,48,82,769.35	15.17226	35.34965055	50	80	100
2019-20	30STE01400	39,09,52,693.55	3,46,48,82,769.35	11.28329	46.63293831	50	80	100
2019-20	SH05A19080	36,34,04,455.30	3,46,48,82,769.35	10.48822	57.12115623		80	100
2019-20	EARTH	35,30,46,239.41	3,46,48,82,769.35	10.18927	67.31042562		80	100
2019-20	30NED02200	33,12,86,707.49	3,46,48,82,769.35	9.561267	76.87169304		80	100
2019-20	30KIN01400	28,96,30,272.12	3,46,48,82,769.35	8.359021	85.23071386			100
2019-20	30VAJ04400	25,62,19,911.20	3,46,48,82,769.35	7.394764	92.62547808			100
2019-20	FO06A13139	25,55,18,539.40	3,46,48,82,769.35	7.394764	100			100

[Samarth_18]

Hi @vs_7 ,

Try to create a measure like this:-

50 % =
CALCULATE (
    COUNT ( 'Table (4)'[customer code] ),
    FILTER ( 'Table (4)', 'Table (4)'[cummulative] <= 50 )
)

80 % =
CALCULATE (
    COUNT ( 'Table (4)'[customer code] ),
    FILTER ( 'Table (4)', 'Table (4)'[cummulative] <= 80 )
)

100 % =
CALCULATE (
    COUNT ( 'Table (4)'[customer code] ),
    FILTER ( 'Table (4)', 'Table (4)'[cummulative] <= 100 )
)

output:-

Thanks,

Samarth