Sum of total conditions

[RanjanThammaiah]

Hello,

I have rows of data that includes ID and Amount.

There 2 condition that needs to be checked.

1. 25% of the total value and number(Count) of the Top ID's contributing to 25%. 

2. TOP 25 ID's contribution value.

Based on these 2 condition if Condition 1 Count of ID is not greater than 25 then need to consider Option 1.

else option 2

I have created this in excel by creating Cumulative % of each Individual ID's. not sure if this can be replicated to BI.

I have given Sample table below.

ID Count	ID	Total	% of Total	% cumulative Total	Cumulative Total	Opp Count_2
1	3002	12500000	5%	5%	12500000	1
2	3173	5201910	2%	7%	17701910	2
3	2707	4431160	2%	9%	22133070.34	3
4	2999	3751168	2%	10%	25884238.6	4
5	2951	3059370	1%	12%	28943608.33	5
6	3306	2256000	1%	13%	31199608.33	6
7	3372	2000000	1%	13%	33199608.33	7
8	3457	1868132	1%	14%	35067740.1	8
9	2156	1801100	1%	15%	36868840.1	9
10	3357	1800000	1%	16%	38668840.1	10
11	3172	1733970	1%	16%	40402810.1	11
12	3387	1733970	1%	17%	42136780.1	12
13	3459	1733970	1%	18%	43870750.1	13
14	3212	1700000	1%	18%	45570750.1	14
15	2845	1651928	1%	19%	47222677.6	15
16	3199	1610000	1%	20%	48832677.6	16
17	3816	1500000	1%	20%	50332677.6	17
18	2637	1431671	1%	21%	51764348.1	18
19	2695	1387176	1%	21%	53151524.1	19
20	3376	1341640	1%	22%	54493164.1	20
21	3242	1330000	1%	22%	55823164.1	21
22	3309	1300000	1%	23%	57123164.1	22
23	3456	1233672	0%	23%	58356836.07	23
24	3406	1200000	0%	24%	59556836.07	24
25	3502	1135750	0%	24%	60692586.42	25
26	3492	1085554	0%	25%	61778140.89	26
27	3460	1040382	0%	25%	62818522.89	27
28	3366	1000000	0%	26%	63818522.89	28
29	3186	1000000	0%	26%	64818522.89	29
30	3359	999431	0%	26%	65817953.89	30
31	2721	953683.5	0%	27%	66771637.39	31
32	3751	900000	0%	27%	67671637.39	32
33	1037	900000	0%	28%	68571637.39	33
34	3422	866985	0%	28%	69438622.39	34
35	2716	866985	0%	28%	70305607.39	35
36	3399	866985	0%	29%	71172592.39	36

[amitchandak]

@RanjanThammaiah , the Windows function can help

example

Cumm = CALCULATE([Net], WINDOW(1,ABS, 0, REL, ADDCOLUMNS(ALLSELECTED('Item'[Brand]),"_net",[Net]),ORDERBY([_net],desc)))
Cumm % = DIVIDE([Cumm], CALCULATE([Net], ALLSELECTED()))
25% = sumx(KEEPFILTERS(ADDCOLUMNS(ALLSELECTED('Item'[Brand]), "_1", [Cumm %])), if([_1]<.25 , [Net], BLANK()))

Pareto Analysis Again, 80% of sales, Order by Measure when REL position is used: https://youtu.be/GpoITi_tRIw

[RanjanThammaiah]

@amitchandak , Thanks for the reply.

Looks like it's not working for me. Kindly check and let me know if am doing it wrongly.