Need help!!! Need to create a calculated column/measure based on top80/Bottom 20 percentage.

[Anonymous]

Hi All,

Need help!!! Need to create a calculated column/measure to segment based on top80/Bottom 20 percentage.

Below the desired output with dataset.

Table name : 'Raw data - Template'

Dataset Column : Month, Supplier Code, Total

Output Column : Cummulative % by Month , T80/ B20

Help to create Cummulative % by month based on the Month column.

Month	Supplier Code	Total	Cummulative % by Month	T 80/ B 20
Jan-19	1234567 - Supplier  - 1	0.104068001	17%	T 80
Jan-19	1234567 - Supplier  - 16	0.08260158	30%	T 80
Jan-19	1234567 - Supplier  13	0.080666	42%	T 80
Jan-19	1234567 - Supplier  - 17	0.080666	55%	T 80
Jan-19	1234567 - Supplier  - 15	0.06696371	66%	T 80
Jan-19	1234567 - Supplier  8	0.0582844	75%	T 80
Jan-19	1234567 - Supplier  - 12	0.03661181	81%	B 20
Jan-19	1234567 - Supplier  - 3	0.02508	85%	B 20
Jan-19	1234567 - Supplier  6	0.0168658	88%	B 20
Jan-19	1234567 - Supplier  7	0.00850269	89%	B 20
Jan-19	1234567 - Supplier  19	0.007656765	90%	B 20
Jan-19	1234567 - Supplier  4	0.00757606	91%	B 20
Jan-19	1234567 - Supplier  11	0.00753594	93%	B 20
Jan-19	1234567 - Supplier  20	0.007005964	94%	B 20
Jan-19	1234567 - Supplier  - 18	0.00657393	95%	B 20
Jan-19	1234567 - Supplier  16	0.00450558	95%	B 20
Jan-19	1234567 - Supplier  - 20	0.00288397	96%	B 20
Jan-19	1234567 - Supplier  - 2	0.00277648	96%	B 20
Jan-19	1234567 - Supplier  17	0.00265766	97%	B 20
Jan-19	1234567 - Supplier  - 4	0.00262432	97%	B 20
Jan-19	1234567 - Supplier  3	0.00253056	98%	B 20
Jan-19	1234567 - Supplier  - 14	0.00219955	98%	B 20
Jan-19	1234567 - Supplier  - 10	0.00176552	98%	B 20
Jan-19	1234567 - Supplier  12	0.00146649	98%	B 20
Jan-19	1234567 - Supplier  1	0.001024	99%	B 20
Jan-19	1234567 - Supplier  - 21	0.00099653	99%	B 20
Jan-19	1234567 - Supplier  - 8	0.00093442	99%	B 20
Jan-19	1234567 - Supplier  - 6	0.00090128	99%	B 20
Jan-19	1234567 - Supplier  - 11	0.00086896	99%	B 20
Jan-19	1234567 - Supplier  - 5	0.00080805	99%	B 20
Jan-19	1234567 - Supplier  9	0.0007868	99%	B 20
Jan-19	1234567 - Supplier  10	0.00072675	99%	B 20
Jan-19	1234567 - Supplier  14	0.00068507	100%	B 20
Jan-19	1234567 - Supplier  5	0.00063547	100%	B 20
Jan-19	1234567 - Supplier  15	0.00063452	100%	B 20
Jan-19	1234567 - Supplier  - 9	0.00050208	100%	B 20
Jan-19	1234567 - Supplier  - 19	0.00041469	100%	B 20
Jan-19	1234567 - Supplier  - 13	0.00012205	100%	B 20
Jan-19	1234567 - Supplier  - 7	0.00007048	100%	B 20
Jan-19	1234567 - Supplier  21	0.0000579	100%	B 20
Jan-19	1234567 - Supplier  18	4.75942E-05	100%	B 20
Jan-19	1234567 - Supplier  2	0.000036	100%	B 20
Feb-19	1234567 - Supplier - 13	0.42222402	23%	T 80
Feb-19	1234567 - Supplier - 12	0.40678127	45%	T 80
Feb-19	1234567 - Supplier -18	0.26491907	60%	T 80
Feb-19	1234567 - Supplier -8	0.17638514	69%	T 80
Feb-19	1234567 - Supplier - 15	0.14531	77%	T 80
Feb-19	1234567 - Supplier -1	0.09266318	82%	B 20
Feb-19	1234567 - Supplier -22	0.082	87%	B 20
Feb-19	1234567 - Supplier - 1	0.0518053	90%	B 20
Feb-19	1234567  - Supplier -1	0.0189252	91%	B 20
Feb-19	1234567 - Supplier -  5	0.01568007	92%	B 20
Feb-19	1234567 - Supplier -27	0.01493414	92%	B 20
Feb-19	1234567 - Supplier -20	0.01371584	93%	B 20
Feb-19	1234567 - Supplier -21	0.0122843	94%	B 20
Feb-19	1234567 - Supplier -6	0.0110732	94%	B 20
Feb-19	1234567 - Supplier -33	0.010382086	95%	B 20
Feb-19	1234567 - Supplier - 16	0.00879987	95%	B 20
Feb-19	1234567 - Supplier -30	0.00739136	96%	B 20
Feb-19	1234567 - Supplier -32	0.00678871	96%	B 20
Feb-19	1234567 - Supplier -10	0.00623175	97%	B 20
Feb-19	1234567 - Supplier - 3	0.00611996	97%	B 20
Feb-19	1234567 - Supplier -2	0.00583615	97%	B 20
Feb-19	1234567 - Supplier - 14	0.00576736	97%	B 20
Feb-19	1234567 - Supplier -9	0.005	98%	B 20
Feb-19	1234567 - Supplier -29	0.00461045	98%	B 20
Feb-19	1234567 - Supplier -28	0.0044995	98%	B 20
Feb-19	1234567 - Supplier -  4	0.00444687	98%	B 20
Feb-19	1234567 - Supplier -12	0.00415143	99%	B 20
Feb-19	1234567 - Supplier -  7	0.00380993	99%	B 20
Feb-19	1234567 - Supplier -17	0.00343391	99%	B 20
Feb-19	1234567 - Supplier -19	0.00284325	99%	B 20
Feb-19	1234567 - Supplier -23	0.00262409	99%	B 20
Feb-19	1234567 - Supplier -14	0.00160919	99%	B 20
Feb-19	1234567 - Supplier - 8	0.00155175	100%	B 20
Feb-19	1234567 - Supplier -16	0.001249	100%	B 20
Feb-19	1234567 - Supplier -  11	0.00100376	100%	B 20
Feb-19	1234567 - Supplier -3	0.00092265	100%	B 20
Feb-19	1234567 - Supplier -25	0.0008568	100%	B 20
Feb-19	1234567 - Supplier -26	0.00082604	100%	B 20
Feb-19	1234567 - Supplier -5	0.000585	100%	B 20
Feb-19	1234567 - Supplier - 9	0.00044601	100%	B 20
Feb-19	1234567 - Supplier -15	0.00042516	100%	B 20
Feb-19	1234567 - Supplier -11	0.00034739	100%	B 20
Feb-19	1234567 - Supplier - 10	0.00033625	100%	B 20
Feb-19	1234567 - Supplier - 2	0.00025908	100%	B 20
Feb-19	1234567 - Supplier -13	0.00021622	100%	B 20
Feb-19	1234567 - Supplier -4	0.00019592	100%	B 20
Feb-19	1234567 - Supplier -31	0.00006751	100%	B 20
Feb-19	1234567 - Supplier -24	0.00006153	100%	B 20
Feb-19	1234567 - Supplier -7	0.00000474	100%	B 20

Any solution is very helpful.

Thanks !!

[CheenuSing · Accepted solution]

Hi @Anonymous ,

Try the following steps.

1.  Using Edit Query Add an Index Column from 1

2.  Create calculated column

       MonthsTotal =
                SUMX ( FILTER ( Data, EARLIER ( Data[Month] ) = Data[Month] ), Data[Total] )
   This calculates the total by month

3. Create Calculated Column

      Month% = Divide(Data[Total], [MonhtsTotal])

     This calculates the % by row of each record in a month.

4. Create the calculated column

    MonthsCum%  =
   1
    - SUMX (
        FILTER (
            ALL ( Data ),
            ( Data[Month] ) = EARLIER ( Data[Month] )
                && ( Data[Index] ) > EARLIER ( Data[Index] )
        ),
        Data[Month%]
    )

   This calculates the Cumulative % by records in each month. Notice that you have to substract from 1 to adjust **bleep** by rows in a month.

5. Finally create calculated column

T80B20 = If( [MonthsCum%_Col] <=.80, "T80","B20")

Replace Data by your table name and field names as per your table.

Cheers

CheenuSing