Search code examples
excelssaspowerbidaxpowerpivot

Sum values from different columns with same parent in DAX

My case is a bit complicated so I'll try to explain it first but the pictures will be more helpful I think. Here is my table : enter image description here

If there is an 'Id' "7409" in a 'Parent', I want the 'Amount' of this 'Id' being added in a new column where, under the same 'Parent', the 'Rate' is "9,25". Obviously the 'Amount' is added while keeping the same 'Group' ('Amount' of 'Group' "5" from 'Id' "7409" with the line of 'Group' "5" from the new 'Id').

For example, the result should be something like this (the column 'AddedValue' is what I need): enter image description here

I tried to make it as clear as possible, let me know if you have questions.

Thanks.

Solution

  • Here is an expression based on your example. enter image description here

    MyCustomColumn = 
IF(RealCase[PremiumRate] = 9,25; 
CALCULATE(
     MIN(RealCase[Montant])
    ;FILTER(RealCase; 
                    RealCase[No_Quittance] = EARLIER(RealCase[No_Quittance])
                &&  RealCase[GL_AccountNBR] = 7409             
                &&  RealCase[RBCD] = EARLIER(RealCase[RBCD])
                &&  RealCase[GL_AccountNBR] <> EARLIER(RealCase[GL_AccountNBR])
     )
); 0)

    Now, you just need to do a sum.

    Regards, Arnaud