Formula Excel to DAX

[mlozano]

Hello, I have a problem migrating a formula from excel to DAX. My excel file is as follows:

The formula that I am trying to replicate is the one in the red text called "NR/hl Cat Mix", in excel it is formulated as follows, it is really very simple:

(Peso SKU Act - Peso SKU Base) * (NR/Hl Cat Base - NR/Hl Total Base)

The formula in Excel is recalculated row by row, that is, every time I filter a dimension in my Excel table, the values ​​are recalculated row by row and depending on the applied filters there is always a new result.

when I try to replicate my excel formula in DAX, the behavior is completely different, I have no problem calculating the values ​​of the subtraction (Peso SKU Act - Peso SKU Base), both are percentage shares of each SKU ID over the total of each volume (LE Volume and ACT Volume), these in Excel as mentioned above, are calculated row by row in Excel, in DAX I do it as a Measure to be able to assign the same behavior from the recalculation that has the result in Excel.

My drawback is that when I subtract both actions in DAX, the result is 0, I have not been able to force the context of the row, I have already thought that the best solution would be a calculated column but I get static results, they are not recalculated according to the applied filters, so when adding, the result is very low or very high.

The shares in DAX, as a measure, I am calculating it like this:

Peso% SKU Act = DIVIDE(SUMX(Worksheet,[ACT Volume]),CALCULATE(SUM(Worksheet[ACT Volume]),ALLSELECTED(Worksheet)))

Peso% SKU Base = DIVIDE(SUMX(Worksheet,[LE Volume]),CALCULATE(SUM(Worksheet[LE Volume]),ALLSELECTED(Worksheet)))

As mentioned, the subtraction between both participations is 100%, so when subtracting them the result is 0. With the results of (NR/Hl Base Cat - NR/Hl Base Total), I have no problem. It's just that when you multiply it by 0, the result of "NR/hl Cat Mix" will always be 0, which is not correct.

I am sharing a sample of the data, which also contains the formula I want to replicate (NR/hl Cat Mix)

Sample Data 

[lbendlin · Accepted solution]

Thank you for providing the sample data. That helps a lot with proposing a potential solution.

"The formula in Excel is recalculated row by row, that is, every time I filter a dimension in my Excel table, the values ​​are recalculated row by row and depending on the applied filters there is always a new result."

That is incorrect. The result is immutable (independent of user input) since it is all calculated in the same row. You can do this as a calculated column or as a Power Query transform. There is no need for a measure.

"It's just that when you multiply it by 0, the result of "NR/hl Cat Mix" will always be 0, which is not correct."

Eh?

See attached for a Power Query based implementation.