Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ao352
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
Anonymous
Not applicable

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

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

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

Hi,

 

Please see the link below containing the .pbix file:

 

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

 

 

Anonymous
Not applicable

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

 

Best

Darek

Hi, 

 

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

Anonymous
Not applicable

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

It works! Thank you 🙂 

Anonymous
Not applicable

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

 

Best

Darek

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors