Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Help to calculate Commulative %

Hi All, I have below table. Root Category Category Count of ID A A1 142   A2 69   A3 25   A4 17   A5 16   A8 5   Requirement is to calcualte cummulative tota...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

    What connection mode data source your report is connected to? I saw you mention that you can't create additional tables in DM, is the data source in DQ or LC connection mode? Or are you unable to create additional tables in DM for some other reason?

    I have created an sample pbix file(see attachment), please check if it is what you want.
    1. Create a calculated column to get the index base on Root Category and Category fields (It is necessary for the cumulative calculation later, use it as a base column for the cumulative calculation)

    Index = RANKX ( 'Table', 'Table'[Category],, ASC, DENSE )

    2. Create two measures to get the cumulative value and cumulative percentage respectively

    Cumulative Total = 
VAR _tab =
    SUMMARIZE (
        'Table',
        'Table'[Root Category],
        'Table'[Category],
        "@sumofid",
            CALCULATE (
                SUM ( 'Table'[ID] ),
                FILTER (
                    ALLEXCEPT('Table','Table'[Root Category]),
                    'Table'[Index] <= SELECTEDVALUE ( 'Table'[Index] )
                )
            )
    )
RETURN
    MAXX ( _tab, [@sumofid] )
    Cumulative Percentage = 
DIVIDE (
    [Cumulative Total],
    CALCULATE ( SUM ( 'Table'[ID] ), ALLEXCEPT ( 'Table', 'Table'[Root Category] ) ),
    0
)

    Best Regards

CNENFRNL's avatar
CNENFRNL
Community Champion
4 years ago

Due to intrinsic attribute of VertiPaq engine under the hood in PBI, rows are not literally ordinal in a table; that's to say, A1 doesn't preceed A2 at all. You must manually assign orders among categories.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi, Thank you for suporting. 

How do we rank category in below case.

 

Root CategoryCategoryID
ACat2
ACat12
ACat22
ARat4
ATiger5
ALion6
ARat34
ATiger55
APig7
BCat8
BRat9
BTiger10
BLion11
BPig15
BCat18
BRat39
BTiger70
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you for support.

     

    Please guide me to achve above requirement (rank the Root Category and Category) with DAX expression. 

     As additional tables can't be created in DM.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

    What connection mode data source your report is connected to? I saw you mention that you can't create additional tables in DM, is the data source in DQ or LC connection mode? Or are you unable to create additional tables in DM for some other reason?

    I have created an sample pbix file(see attachment), please check if it is what you want.
    1. Create a calculated column to get the index base on Root Category and Category fields (It is necessary for the cumulative calculation later, use it as a base column for the cumulative calculation)

    Index = RANKX ( 'Table', 'Table'[Category],, ASC, DENSE )

    2. Create two measures to get the cumulative value and cumulative percentage respectively

    Cumulative Total = 
VAR _tab =
    SUMMARIZE (
        'Table',
        'Table'[Root Category],
        'Table'[Category],
        "@sumofid",
            CALCULATE (
                SUM ( 'Table'[ID] ),
                FILTER (
                    ALLEXCEPT('Table','Table'[Root Category]),
                    'Table'[Index] <= SELECTEDVALUE ( 'Table'[Index] )
                )
            )
    )
RETURN
    MAXX ( _tab, [@sumofid] )
    Cumulative Percentage = 
DIVIDE (
    [Cumulative Total],
    CALCULATE ( SUM ( 'Table'[ID] ), ALLEXCEPT ( 'Table', 'Table'[Root Category] ) ),
    0
)

    Best Regards