Sorting and calculating Cumulative value

[Navin_C]

Hello All,

I have data appearing as below ... i want to sort based on Column A categories and then calculate 70% cumulative value for each category.

Kindly suggest...

Column A	Column B
TS	2299
TS	919
CL-ACL-QC	2546
CL-ACL-QC	1289
QC	1871
TS	2035
QC	876
TS	1509
QC	3435
TS	1334
CL-ACL-TS	3495
CL-ACL-TS	1600
CL-ACL-QC	1650
TS	1335
CL-ACL-QC	1404
CL-ACL-TS	2434
TS	1762
CL-ACL-TS	1136
TS	1989
TS	1303
CL-ACL-TS	2517
TS	1645
ED TS	2342
ED TS	2186
ED TS	2332
ED TS	1546
ED TS	248
ED TS	1288
ED TS	892
ED PR	3389
ED PR	1789
ED PR	3034
ED PR	3745
ED PR	2820
ED PR	0

[Navin_C]

Hello @Jihwan_Kim 

Attached is my sample data ... I want to sort first based on Q column, then based on Cell2 colum

n and then pages in descending order. As in excel scrennshot below

Then calculate cumulative % for Pages based on above sorting for each category. Kindly refer below.

Q	Cell 2	Pages	Cumulative %
Q1	ED PR	5231	8.37%
Q1	ED PR	4401	15.41%
Q1	ED PR	4310	22.31%
Q1	ED PR	4074	28.83%
Q1	ED PR	3817	34.93%
Q1	ED PR	3745	40.93%
Q1	ED PR	3463	46.47%
Q1	ED PR	3389	51.89%
Q1	ED PR	3132	56.90%
Q1	ED PR	3034	61.76%
Q1	ED PR	2867	66.34%
Q1	ED PR	2839	70.89%
Q1	ED PR	2820	75.40%
Q1	ED PR	2777	79.84%
Q1	ED PR	2488	83.82%
Q1	ED PR	2477	87.79%
Q1	ED PR	2380	91.60%
Q1	ED PR	1869	94.59%
Q1	ED PR	1789	97.45%
Q1	ED PR	1366	99.64%
Q1	ED PR	228	100.00%
Q1	ED PR	0	100.00%
Q1	ED TS	3968
Q1	ED TS	3872
Q1	ED TS	3428
Q1	ED TS	3300
Q1	ED TS	3038
Q1	ED TS	2962
Q1	ED TS	2810
Q1	ED TS	2690
Q1	ED TS	2614
Q1	ED TS	2348
Q1	ED TS	2342
Q1	ED TS	2336
Q1	ED TS	2332
Q1	ED TS	2186
Q1	ED TS	2156
Q1	ED TS	2050
Q1	ED TS	1958
Q1	ED TS	1798
Q1	ED TS	1780
Q1	ED TS	1770
Q1	ED TS	1662
Q1	ED TS	1606
Q1	ED TS	1546
Q1	ED TS	1544
Q1	ED TS	1518
Q1	XPP-QC	3617
Q1	XPP-QC	3438
Q1	XPP-QC	3433
Q1	XPP-QC	3387
Q1	XPP-QC	3152
Q1	XPP-QC	3122
Q1	XPP-QC	3001
Q1	XPP-QC	2832
Q1	XPP-QC	2829
Q1	XPP-QC	2762
Q1	XPP-QC	2685
Q1	XPP-QC	2683
Q1	XPP-QC	2554
Q1	XPP-QC	2302
Q1	XPP-QC	2203
Q1	XPP-QC	1912
Q1	XPP-QC	1871
Q1	XPP-QC	1323
Q1	XPP-TS	2694
Q1	XPP-TS	2653
Q1	XPP-TS	2648
Q1	XPP-TS	2620
Q1	XPP-TS	2614
Q1	XPP-TS	2589
Q1	XPP-TS	2447
Q1	XPP-TS	2447
Q1	XPP-TS	2414
Q1	XPP-TS	2332
Q1	XPP-TS	2240
Q1	XPP-TS	2225
Q1	XPP-TS	2224
Q1	XPP-TS	2222
Q1	XPP-TS	2194
Q1	XPP-TS	2187
Q1	XPP-TS	2112
Q1	XPP-TS	2102
Q1	XPP-TS	2095
Q1	XPP-TS	2068
Q1	XPP-TS	2035
Q1	XPP-TS	2026
Q1	XPP-TS	1989
Q1	XPP-TS	1923
Q1	XPP-TS	1838
Q1	XPP-TS	1793
Q1	XPP-TS	1762
Q1	XPP-TS	1725
Q1	XPP-TS	1705
Q1	XPP-TS	1668
Q1	XPP-TS	1648
Q1	XPP-TS	1601
Q2	ED PR	3757
Q2	ED PR	3137
Q2	ED PR	3032
Q2	ED PR	2444
Q2	ED PR	2443
Q2	ED PR	2246
Q2	ED PR	2122
Q2	ED PR	2049
Q2	ED PR	1987
Q2	ED PR	1965
Q2	ED PR	1791
Q2	ED PR	1525
Q2	ED PR	1517
Q2	ED PR	1517
Q2	ED PR	1352
Q2	ED TS	2658
Q2	ED TS	2210
Q2	ED TS	2172
Q2	ED TS	1812
Q2	ED TS	1538
Q2	ED TS	1524
Q2	ED TS	1264
Q2	ED TS	1248
Q2	ED TS	1220
Q2	ED TS	1198
Q2	ED TS	1192
Q2	ED TS	1172
Q2	ED TS	1140
Q2	ED TS	1112
Q2	ED TS	1020
Q2	ED TS	1008
Q2	ED TS	984
Q2	ED TS	828
Q2	ED TS	808
Q2	ED TS	808
Q2	ED TS	808
Q2	ED TS	792
Q2	ED TS	758
Q2	ED TS	708
Q2	XPP-QC	5178
Q2	XPP-QC	5122
Q2	XPP-QC	5055
Q2	XPP-QC	5031
Q2	XPP-QC	4139
Q2	XPP-QC	3915
Q2	XPP-QC	3230
Q2	XPP-QC	2978
Q2	XPP-QC	2951
Q2	XPP-QC	2732
Q2	XPP-QC	2583
Q2	XPP-QC	2546
Q2	XPP-QC	2192
Q2	XPP-QC	2099
Q2	XPP-QC	1896
Q2	XPP-TS	2968
Q2	XPP-TS	2910
Q2	XPP-TS	2832
Q2	XPP-TS	2738
Q2	XPP-TS	1881
Q2	XPP-TS	1502
Q2	XPP-TS	1487
Q2	XPP-TS	1481
Q2	XPP-TS	1478
Q2	XPP-TS	1380
Q2	XPP-TS	1356
Q2	XPP-TS	1330
Q2	XPP-TS	1318
Q2	XPP-TS	1314
Q2	XPP-TS	1265
Q2	XPP-TS	1256
Q2	XPP-TS	1236
Q2	XPP-TS	1198
Q2	XPP-TS	1168
Q2	XPP-TS	1145
Q2	XPP-TS	933
Q2	XPP-TS	904
Q2	XPP-TS	829
Q2	XPP-TS	752
Q2	XPP-TS	424
Q3	ED PR	1348
Q3	ED PR	924
Q3	ED PR	887
Q3	ED PR	870
Q3	ED PR	868
Q3	ED PR	844
Q3	ED PR	807
Q3	ED PR	797
Q3	ED PR	656
Q3	ED PR	619
Q3	ED PR	490
Q3	ED PR	429
Q3	ED PR	54
Q3	ED TS	933
Q3	ED TS	875
Q3	ED TS	818
Q3	ED TS	751
Q3	ED TS	727
Q3	ED TS	714
Q3	ED TS	701
Q3	ED TS	691
Q3	ED TS	619
Q3	ED TS	606
Q3	ED TS	565
Q3	ED TS	533
Q3	ED TS	526
Q3	ED TS	487
Q3	ED TS	421
Q3	ED TS	407
Q3	ED TS	404
Q3	ED TS	401
Q3	ED TS	391
Q3	ED TS	224
Q3	ED TS	188
Q3	ED TS	178
Q3	ED TS	97
Q3	ED TS	50
Q3	XPP-QC	2339
Q3	XPP-QC	2323
Q3	XPP-QC	2269
Q3	XPP-QC	2048
Q3	XPP-QC	2026
Q3	XPP-QC	1952
Q3	XPP-QC	1927
Q3	XPP-QC	1912
Q3	XPP-QC	1742
Q3	XPP-QC	1695
Q3	XPP-QC	1485
Q3	XPP-QC	1423
Q3	XPP-QC	1409
Q3	XPP-QC	1401
Q3	XPP-QC	1383
Q3	XPP-QC	1365
Q3	XPP-QC	1358
Q3	XPP-QC	1351
Q3	XPP-QC	1315
Q3	XPP-QC	1180
Q3	XPP-QC	1157
Q3	XPP-TS	1386
Q3	XPP-TS	1253
Q3	XPP-TS	1062
Q3	XPP-TS	1048
Q3	XPP-TS	1033
Q3	XPP-TS	1022
Q3	XPP-TS	983
Q3	XPP-TS	964
Q3	XPP-TS	940
Q3	XPP-TS	914
Q3	XPP-TS	896
Q3	XPP-TS	867
Q3	XPP-TS	866
Q3	XPP-TS	864
Q3	XPP-TS	849
Q3	XPP-TS	842
Q3	XPP-TS	841
Q3	XPP-TS	834
Q3	XPP-TS	806
Q3	XPP-TS	802
Q3	XPP-TS	800
Q3	XPP-TS	789
Q3	XPP-TS	788
Q3	XPP-TS	764

[Jihwan_Kim · Accepted solution]

Hi,

Please check the attached file and the below picture whether it suits your requirement.

Cumulative % by Q and by Cell: =
DIVIDE (
    CALCULATE (
        SUM ( Data[Pages] ),
        WINDOW (
            1,
            ABS,
            0,
            REL,
            SUMMARIZE ( ALL ( Data ), Q[Q], 'Cell'[Cell 2], Data[Pages] ),
            ORDERBY ( CALCULATE ( SUM ( Data[Pages] ) ), DESC ),
            DEFAULT,
            PARTITIONBY ( Q[Q], 'Cell'[Cell 2] )
        )
    ),
    CALCULATE (
        SUM ( Data[Pages] ),
        WINDOW (
            1,
            ABS,
            -1,
            ABS,
            SUMMARIZE ( ALL ( Data ), Q[Q], 'Cell'[Cell 2], Data[Pages] ),
            ORDERBY ( CALCULATE ( SUM ( Data[Pages] ) ), DESC ),
            DEFAULT,
            PARTITIONBY ( Q[Q], 'Cell'[Cell 2] )
        )
    )
)

[Navin_C]

Thanks @Jihwan_Kim This worked perfectly !!!

[Navin_C]

@Jihwan_Kim  Thanks, will test out ...its surely getting closure

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

WINDOW function (DAX) - DAX | Microsoft Learn

Expected result measure: =
CALCULATE (
    SUM ( Data[Column B] ),
    FILTER (
        Data,
        DIVIDE (
            CALCULATE (
                SUM ( Data[Column B] ),
                WINDOW (
                    1,
                    ABS,
                    0,
                    REL,
                    SUMMARIZE ( ALL ( Data ), Category[Column A], Data[Column B] ),
                    ORDERBY ( CALCULATE ( SUM ( Data[Column B] ) ), DESC ),
                    DEFAULT,
                    PARTITIONBY ( Category[Column A] )
                )
            ),
            CALCULATE (
                SUM ( Data[Column B] ),
                WINDOW (
                    1,
                    ABS,
                    -1,
                    ABS,
                    SUMMARIZE ( ALL ( Data ), Category[Column A], Data[Column B] ),
                    ORDERBY ( CALCULATE ( SUM ( Data[Column B] ) ), DESC ),
                    DEFAULT,
                    PARTITIONBY ( Category[Column A] )
                )
            )
        ) <= 0.7
    )
)