Forum Discussion
Helper IIIError migrating Excel formula to DAX
Hello, I find myself migrating some formulas from Excel to DAX, one of them is the following:
NR/hl Cat Mix = (Peso SKU Act - Peso SKU Base) * (NR/Hl Cat Base - NR/Hl Total Base)
To replicate the previous formula in DAX I must first calculate both shares, I am doing it 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)))
Both "NR/Hl Cat Base" and "NR/Hl Total Base" are results that are as values in excel, they would have the same status in Power BI.
My inconvenience is that I have to subtract the participations, the sum of both is 100% because it adds the participation of all the SKUs and when subtracting them it gives me 0. In excel the same thing does not happen, the calculation is made SKU by SKU (Row by Row) , so the share of each SKU (each row) is subtracted, in which case the result is no longer 0. How can I achieve the same behavior in DAX?
I share a sample of the data with the fields involved in the calculation SAMPLE DATA
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]) ) ) ))To make it row by row force row context
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]) ) ) ))To make it row by row force row context
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])