Forum Discussion

ao352's avatar
ao352
Frequent Visitor
7 years ago
Solved

MINX not considering negative values

I created the following measure to calculate the % change in the weight of food items each week.

 

Running % Change =
VAR Latest_Week = CALCULATE(MAXA('Weight'[Week]),'Weight'[Weight (Kg)]<>0)
VAR First_Recorded_Week = CALCULATE(MINA('Weight'[Week]),'Weight'[Weight (Kg)]<>0)
VAR Weight_FRW = CALCULATE(SUMMARIZE('Weight','Weight'[Weight (Kg)]),'Weight'[Week]=First_Recorded_Week)
VAR Weight_LW = CALCULATE(SUMMARIZE('Weight','Weight'[Weight (Kg)]),'Weight'[Week]=Latest_Week)

RETURN
(Weight_LW - Weight_FRW)/Weight_FRW

 

I added the measure to a graph and was able to see the percentage change, for which some values are negative, so -4%).

 

I want to now display on a card, the name of the item which has the greatest % loss in weight between the first week and current week (so the lowest percentage change). I thought a MINX measure would work in this scenario, but it hasn't for me. Values below 0 are not being picked up, so the wrong product name is being displayed.

 

I would like help on this please.

measure
minx
negative
ranking
value
  • Anonymous's avatar
    Anonymous
    7 years ago

    I think you should change your measure to this:

     

    Running % Change = 
VAR __onlyOneFruitVisible = HASONEFILTER( Products[Name] )
VAR Latest_Week = 
    CALCULATE(
        MAX( 'Products'[Week] ),
        'Products'[Weight] > 0
    )
VAR First_Recorded_Week = 
    CALCULATE(
        MIN( 'Products'[Week] ),
        'Products'[Weight] > 0
    )
VAR Weight_FRW =
    CALCULATE(
        VALUES( 'Products'[Weight] ),
        'Products'[Week] = First_Recorded_Week
    )
VAR Weight_LW =
    CALCULATE(
        VALUES( 'Products'[Weight] ),
        'Products'[Week] = Latest_Week
    )
RETURN
    if( __onlyOneFruitVisible,
        DIVIDE( Weight_LW - Weight_FRW, Weight_FRW )
    )

    Does this calculation make sense for many fruits at the same time? Probably not... Hence the IF guard clause.

     

    First measure:

    Greatest Decrease = 
var __decrease =
MINX(
    ALLSELECTED( Products[Name] ),
    [Running % Change]
)
var __decreaseFormatted = format( __decrease, "Percent" )
var __isDecrease = __decrease < 0
return
    "The "
    & if( __isDecrease, "greatest decrease", "lowest increase")
    & " in weight is " & __decreaseFormatted & "."

    Second measure:

    Fruit with Greatest Decrease = 
var __prodName =
    MAXX(
        TOPN(
            1,
            ALLSELECTED( Products[Name] ),
            [Running % Change],
            ASC
        ),
        Products[Name]
    )
var __decrease =
    MINX(
        ALLSELECTED( Products[Name] ),
        [Running % Change]
    )
var __isDecrease = __decrease < 0
var __result =
    "Fruit with the "
    & if( __isDecrease, "greatest decrease", "lowest increase")
    & " in weight is " & __prodName & "."
return
    __result

    Here's the result. Just put the measures in two different cards. The measures react to the slicer on the right.

    Best

    Darek

7 Replies