cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ao352 Frequent Visitor
Frequent Visitor

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: MINX not considering negative values

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.Fruits.PNG

Best

Darek

7 REPLIES 7
Super User
Super User

Re: MINX not considering negative values

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 Frequent Visitor
Frequent Visitor

Re: MINX not considering negative values

Hi,

 

Please see the link below containing the .pbix file:

 

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

 

 

Super User
Super User

Re: MINX not considering negative values

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

 

Best

Darek

ao352 Frequent Visitor
Frequent Visitor

Re: MINX not considering negative values

Hi, 

 

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

Super User
Super User

Re: MINX not considering negative values

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

 

Best

Darek

Super User
Super User

Re: MINX not considering negative values

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.Fruits.PNG

Best

Darek

ao352 Frequent Visitor
Frequent Visitor

Re: MINX not considering negative values

It works! Thank you Smiley Happy 

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 311 members 3,102 guests
Please welcome our newest community members: