Forum Discussion

misharaina's avatar
misharaina
Icon for Advocate I rankAdvocate I
4 years ago
Solved

SUMX not working as expected

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.
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    misharaina , Are you only using Product in visual. If you are using more columns use summarize and add those too

     

    example

    sumx(Addcolumns(Summatize(Fact, Dim[Col1], Dim2[Col2]), "_1",[Measure]) , [_1])

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    misharaina , Are you only using Product in visual. If you are using more columns use summarize and add those too

     

    example

    sumx(Addcolumns(Summatize(Fact, Dim[Col1], Dim2[Col2]), "_1",[Measure]) , [_1])

  • misharaina's avatar
    misharaina
    Icon for Advocate I rankAdvocate I
    4 years ago

    Hi Amit,

     

    I am using a parameter.

    So my code is - 

    RETURN SWITCH(SELECTEDVALUE('Metric (Support)'[Select Metric]),
    "Revenue ($M)", Result/10^6,
    IF (HASONEVALUE('Parameter Table'[Fields]),result,
    Sumx('Parameter', Result)))

    Best

    Misha