Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
sabrinahuitron
New Member

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:

AS IS ITEM.png

 

 

 

 

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:

Picture2.png

 

 

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:

AS IS.PNG

 

 

 

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

Thank you,

Sabrina H.

1 ACCEPTED SOLUTION
v-chenwuz-msft
Community Support
Community Support

Hi @sabrinahuitron ,

 

Please try this:

 

 

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

 

 

result:

 

vchenwuzmsft_0-1663147158901.png

 

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.

View solution in original post

1 REPLY 1
v-chenwuz-msft
Community Support
Community Support

Hi @sabrinahuitron ,

 

Please try this:

 

 

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

 

 

result:

 

vchenwuzmsft_0-1663147158901.png

 

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.