How to calculate sum over by partitioning rows in PowerBI ?

[dx249]

Hello Everyone,

I greatly appriciate your help here, I am trying to get the sum of rows partition by row value in powerBI as we do in SQL

SUM(ORDER_QUANTITY) over (Partition by Product_code)

for the below table.

I could not find it to do in powerBI, how to do in PowerBI? Help me here

I tried 

Desired_output = calculate(sum(table[ORDER_QUANTITY]),filter(allselected(table),table[PRODUCT_CODE]=MAX(table[PRODUCT_CODE])))

Also tried

Desired_output = calculate(sum(table[ORDER_QUANTITY]),allexcept(table, table[PRODUCT_CODE]))

But getting wrong values.

Thanks in Advance,
DataDX.

[v-jianboli-msft · Accepted solution]

Hi @dx249 ,

If you want the output changed by the slicer, you need to use a measrue.

The calculated column will not change according to the slicer.

Based on your description, I have created a simple sample:

Please try:

Output = 
CALCULATE(SUM('Table'[ORDER_QUANTITY]),REMOVEFILTERS('Table'[NEW_C],'Table'[ORDER_QUANTITY]))

Final output:

Best Regards,

Jianbo Li

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

[Ahmedx]

Share sample pbix file to help you.

[dx249]

Hi @Ahmedx ,

I undestood the issue, it was happening because the calculated column getting sum again, so getting the high(wrong) values.

But still i have an issue,could you please help me.  The calculated column is not changing according to the slicer ( for example, i have a YEAR date sclicer, whenever i select the different year the calculated column is not changing and it is always showing for entire period not for the selected year), how can i change this?

Thanks in Advance

[dx249]

Hi Ahmedx,

Thanks for the resposne, I tried your answer, but still i am getting the wrong values like this below

Also, I tried this one, still getting the wrong values.

Total  = 
    CALCULATE(
        SUM('table'[ORDER_QUANTITY]),
        FILTER(
            'table',
            'table'[Product_code] = EARLIER('table'[Product_code])
            )   
)

Thanks in Advance.

[Ahmedx]

pls try this