CODEWITHSUNDEEP
excel-formulapowerbidax
Alejandro Chaves
Alejandro Chaves

Reputation: 43

Migrating Excel formula to DAX

I find myself replicating the following Excel formula in DAX:

NR/hl Cat Mix = (Peso% SKU Act - Peso% SKU Base) * (NR/Hl Cat Base - NR/Hl Total Base)

This formula includes the subtraction of two shares "Peso% SKU Act" and "Peso% SKU Base", which I am calculating in DAX as follows:

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)))

The result of both shares is 100%

My drawback is that the calculation made in excel does not take into account the total result of the participation (100%), but only takes into account the result of each SKU.

Result by SKU in Excel Example

enter image description here

In Power BI I have not managed to get my result to have the same behavior as the calculation has in Excel, so whenever I subtract the shares that I have in DAX my result is 0 since the total of each shares is 100%

My code in DAX is like this:

NR/hl Cat Mix = (Peso% SKU Act - Peso% SKU Base) * (NR/Hl Cat Base - NR/Hl Total Base)

Very similar to Excel, but behaves like this:

NR/hl Cat Mix = (100% - 100%) * (382.4 - 344.7) = ( 0 ) * ( 37.7 ) = Result DAX = 0

Correct result evidenced in Excel is 2.1

How can I modify my code so that it has the same behavior and dynamics that Excel has and gives me the correct result?

Sample Data Excel

Upvotes: 1

Views: 91

Answers (1)

Alejandro Chaves
Alejandro Chaves

Reputation: 43

To perform this calculation it is necessary to force the row context as follows:

NR/hl Cat Mix = SUMX(Table, (Peso% SKU Act - Peso% SKU Base) * (NR/Hl Cat Base - NR/Hl Total Base)))

Upvotes: 0

Related Questions