Hi, I am trying to display the "Row total of vol/mix impact" using sumx but the row total is always displayed as Zero. Out of the multiple versions displayed on the slicer, the user will select two versions to compare the metrics and calculate the vol/mix impact.
lets call the last version "minversion" and the new version "maxversions". Here is the formula for getting the last and the new versions -
--------------------------------------------------------------------
VAR MinVersion = // previous version
LOOKUPVALUE('Version (Control)'[Version], 'Version (Control)'[Version Sort],
CALCULATE(MIN('Version (Control)'[Version Sort]), ALLSELECTED('Version (Control)'[Version Sort]), ALLSELECTED('Version (Control)'[Version])))
VAR MaxVersion = // new version
LOOKUPVALUE('Version (Control)'[Version], 'Version (Control)'[Version Sort],
CALCULATE(MAX('Version (Control)'[Version Sort]), ALLSELECTED('Version (Control)'[Version Sort]), ALLSELECTED('Version (Control)'[Version])))
//Next, we determine the last version price and the new version price using the formula below -
VAR MinVersionVar1 = //P1
CALCULATE(
Price,
'Version (Control)'[Version]= MinVersion,
ALL('Version (Control)'[Version Sort])
)
VAR MaxVersionVar1 = // P2
CALCULATE(
Price,
'Version (Control)'[Version] = MaxVersion,
ALL('Version (Control)'[Version Sort])
)
//Now we calculate the minversion and maxversion weighted volume as below -
VAR MinVersionVar2 = //Vol1
CALCULATE(
Vol1/Calculate(Vol1,allselected()),
'Version (Control)'[Version]= MinVersion,
ALL('Version (Control)'[Version Sort])
)
VAR MaxVersionVar2 = //Vol2
CALCULATE(
Vol2/Calculate(Vol2,allselected()),
'Version (Control)'[Version] = MaxVersion,
ALL('Version (Control)'[Version Sort])
)
// Next we calculate the average Price for both versions
VAR MinVersionVar3 = //Average Price
CALCULATE(
Calculate(Price,allselected()),
'Version (Control)'[Version] = MinVersion,
ALL('Version (Control)'[Version Sort])
)
VAR MaxVersionVar3 = //Average Price
CALCULATE(
Calculate(Price,allselected()),
'Version (Control)'[Version] = MaxVersion,
ALL('Version (Control)'[Version Sort])
)
// The formula that we have for price volume mix is as follows -
VAR Result = 0.5*((MaxVersionVar1-MaxVersionVar3)+(MinVersionVar1-MaxVersionVar3))*(MaxVersionVar2 - MinVersionVar2)
// Here is where I am trying to return the final answer. At an individual product level, the variable "result" gives the correct answer but the row total is always 0. I want the row total to sum up all the vol/mix impact -
IF (HASONEVALUE('Product(Control)'[Product),result,
Sumx(VALUES('Product (Control)'[Product]),Result)))
-----------------------------------------------------------------------------------------------------------------
// Another thing to note here is that the price and volume tables are different than the product table.
We have a product table that is related to the price and volume table.
