Forum Discussion

Navin_C's avatar
Navin_C
Regular Visitor
2 years ago
Solved

Sorting and calculating Cumulative value

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 T...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    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] )
        )
    )
)
Jihwan_Kim's avatar
Jihwan_Kim
Super User
2 years ago

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's avatar
Navin_C
Regular Visitor
2 years ago

Thanks Jihwan_Kim This worked perfectly !!!