Forum Discussion
Helper IIIHelp with formula from Excel 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
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?
Data sample : Example data in excel
mlozano , if these two measures, then the calculation is fine. but if they are columns then it not fine
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)))
Then these should be like
NR/hl Cat Mix = (Peso% SKU Act - Peso% SKU Base) * ( Divide(Sum(worksheet[NR] , Sum(worksheet[Hl Cat Base]) ) -
Divide(Sum(worksheet[NR] , Sum(worksheet[Hl Total Base]) ) ) ))Force row-level context using
Sumx(summarize(Table, Table[Month], [cat], [Category], [Pack Size, [Brand Pack size]) , [NR/hl Cat Mix ])
or
Sumx(Addcolumns( summarize(Table, Table[Month], [cat], [Category], [Pack Size, [Brand Pack size]) ,
"_1" , [NR/hl Cat Mix ]) ,[_1])
1 Reply
- amitchandak
Super User
mlozano , if these two measures, then the calculation is fine. but if they are columns then it not fine
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)))
Then these should be like
NR/hl Cat Mix = (Peso% SKU Act - Peso% SKU Base) * ( Divide(Sum(worksheet[NR] , Sum(worksheet[Hl Cat Base]) ) -
Divide(Sum(worksheet[NR] , Sum(worksheet[Hl Total Base]) ) ) ))Force row-level context using
Sumx(summarize(Table, Table[Month], [cat], [Category], [Pack Size, [Brand Pack size]) , [NR/hl Cat Mix ])
or
Sumx(Addcolumns( summarize(Table, Table[Month], [cat], [Category], [Pack Size, [Brand Pack size]) ,
"_1" , [NR/hl Cat Mix ]) ,[_1])