MINX not considering negative values

[ao352]

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.

[Anonymous]

Please give us a link to an example file with your model and at least some data that will illustrate the issue. You can send a OneDrive or Google Drive link. Or Dropbox... you name it.

Thanks.

Best

Darek

[ao352]

Hi,

Please see the link below containing the .pbix file:

https://drive.google.com/file/d/1KyNpW9IeJiSId3q_WkZQC0XkXYEwDSiW/view?usp=drivesdk

[Anonymous]

Well, Google does not let me access this file...

Best

Darek

[ao352]

Hi, 

Sorry, I've granted access, are you able to download the file now?

[Anonymous · Accepted solution]

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

[ao352]

It works! Thank you 🙂 

[Anonymous]

Yeah... I could now. I'll have a look. Bear with me.

Best

Darek