DAX equivalent of Excel formula

[Anonymous]

Hello, I'm new to Power BI and so I have a problem with translating Excel formula to DAX.

My problem is that i have some data about sold products.

Some of orders included more than 1 product and usually they were final price was reduced by some discount.

What I need to do is to separate this discount so that it falls on a given product according to its share in the total sales amount including this product (before taking into account the discount).

Example:

Let's take product with index 20 into consideration.

Final price is $109.20 but sum of all products in this order is $114.95. It gives us $5.75 discount.

So to determine discount for each product (let's say for product in row 24) I need to make this calculation:

34.99 / 114.95 * 5.75 = $1.75 discount for this specific product.

My Excel formula to count this is:

=IF(COUNTIF(A:A,A28)=1,(E28-G28),(G28/SUMIF(A:A,A28,G:G))*(E28-SUMIF(A:A,A28,G:G)))

Any idea how can I translate it to DAX to add similar Discount Column?

[marveling · Accepted solution]

Another alternative is to use ALLEXCEPT to create the 'Line Discount' calculated column:

Line Discount = 
VAR SumProduct =
    CALCULATE (
        SUM ( 'Table'[Product Price] ),
        ALLEXCEPT ( 'Table', 'Table'[Index] )
    )
RETURN
    'Table'[Product Price] / SumProduct * ( 'Table'[Final Price] - SumProduct )

Hope it helps.

[grantsamborn]

Maybe try a column like this.

Line Discount =
VAR _SumOfOrder =
CALCULATE(
SUM( 'Sales2'[Product Price] ),
FILTER(
'Sales2',
'Sales2'[Index] = EARLIER( 'Sales2'[Index] )
)
)
RETURN
'Sales2'[Product Price] / _SumOfOrder * ('Sales2'[Final Price] - _SumOfOrder)