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
Icon for Super User rankSuper User
2 years ago

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
    )
)