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 

[v-cgao-msft · Accepted solution]

Hi @mlozano ,

Please try this.

NR/hl Cat Mix1 = 
SUMX(
SUMMARIZE(
    'Worksheet',
    'Worksheet'[Field 1 Name],
    'Worksheet'[Field 2 Name],
    'Worksheet'[Field 3 Name],
    'Worksheet'[Field 4 Name],
    'Worksheet'[Field 5 Name],

...

    'Worksheet'[NR/Hl Cat Base],
    'Worksheet'[NR/Hl Total  Base],
    "Act",[Peso% SKU Act],
    "Base",[Peso% SKU Base]
),
([Act]-[Base])*('Worksheet'[NR/Hl Cat Base]-'Worksheet'[NR/Hl Total  Base])
)

Attached PBIX file for reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[mlozano]

The solution is perfect, thanks!