Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Ungroup a Running total

Hello everyone.

I have the following data:

FLVRITEM_SAPFecha Semana#SemanaCalculo INV final
CHOCOLATE30003391510/09/20220547
CHOCOLATE30003391517/09/20221177
CHOCOLATE30003391524/09/20222-11
CHOCOLATE30003391501/10/20223383
CHOCOLATE30003391610/09/20220240
CHOCOLATE30003391617/09/202217
CHOCOLATE30003391624/09/202228
CHOCOLATE30003391601/10/20223-92
CHOCOLATE30003391810/09/202202101
CHOCOLATE30003391817/09/20221222
CHOCOLATE30003391824/09/20222-61
CHOCOLATE30003391801/10/20223-21

 

As you can see, there are 3 different codes (ITEM SAP) and each week (from 0 to 3) has a different value for it.

I try to apply a cumulative sum with DAX for each of the codes and I get the following result:

 

 

 

 

I used the following formula:

INV Final = CALCULATE(SUMX('table',[Calculate INV final]), FILTER(ALL('table'), 'table'[#Week] <= MAX('table'[#Week]))))


This is the desired result. However, I am looking for a second view where the codes are grouped ("ITEM SAP"), summing the value of the 3 codes for each week. As follows:

 

 

The goal is that a cumulative sum can also be performed by FLVR. But leaving the same formula, the result is not the desired one.
I also tried with the following formula:
INV Final Flvr = CALCULATE([Calculate INV final], FILTER(ALL('table'), 'table'[#Week] <= MAX('table'[#Week])))).
But I get this result:

 

 

 

Idon't understand where is the error. Please help me to solve this.

Thank you,

Sabrina H.

Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    3 years ago

    Hi Anonymous ,

     

    Please try this:

     

     

    INV final =
CALCULATE (
    SUM ( 'Table'[Calculo INV final] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[FLVR], 'Table'[ITEM_SAP] ),
        [#Semana] <= MAX ( 'Table'[#Semana] )
    )
)

     

     

    result:

     

     

    Pbix in the post you created 

    Suma acumulada con una agrupación de datos - Microsoft Power BI Community

     

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    3 years ago

    Hi Anonymous ,

     

    Please try this:

     

     

    INV final =
CALCULATE (
    SUM ( 'Table'[Calculo INV final] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[FLVR], 'Table'[ITEM_SAP] ),
        [#Semana] <= MAX ( 'Table'[#Semana] )
    )
)

     

     

    result:

     

     

    Pbix in the post you created 

    Suma acumulada con una agrupación de datos - Microsoft Power BI Community

     

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.