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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors