Forum Discussion

Talal141218's avatar
Talal141218
Helper III
2 years ago
Solved

Dynamic Cumulative or Total Ranning

Hi Profis, 

I have question regarding to cumulative measure. I create i cumulative measure as below : 

RT = CALCULATE([CM_Stock_Amount_ExclRes_piec], FILTER(ALLSELECTED(Dim_Item[ItemNo]), Dim_Item[ItemNo] <=max(Dim_Item[ItemNo]))). In Addition when i sort the items rely on the amounts the Formula will not works anymore  , please look at the Pics: 
Before                                                                                                 

After sorting depend on Quantity : 

Note : Earlier measure dos't work with my Power BI

Thanks in Advance 🙂 


 

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Talal141218 ,

    Please try below steps:

    1. below is my test table

    Table:

    2. create a measure with below dax formula

    cumulative =
VAR cur_quantity =
    SELECTEDVALUE ( 'Table'[Quantity] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), [Quantity] >= cur_quantity )
RETURN
    SUMX ( tmp, [Quantity] )

    3. add a table visual with table fields and mesure

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Talal141218 ,

    It's normal, there's nothing wrong with it.

    For your measure calculation logic, it does a cumulative sum based on your "Item Number" field, when you sort the "Quantity" field in the table visual, it doesn't change the logic of the calculation.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Talal141218's avatar
      Talal141218
      Helper III
      2 years ago

      Thanks for your answer. could you please share with me the formula, which I can  sort the cumulative amount. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Talal141218 ,

        Please try below steps:

        1. below is my test table

        Table:

        2. create a measure with below dax formula

        cumulative =
VAR cur_quantity =
    SELECTEDVALUE ( 'Table'[Quantity] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), [Quantity] >= cur_quantity )
RETURN
    SUMX ( tmp, [Quantity] )

        3. add a table visual with table fields and mesure

        Please refer the attached .pbix file.

         

        Best regards,
        Community Support Team_Binbin Yu
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.