Forum Discussion
MINX not considering negative values
- Anonymous7 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 __resultHere's the result. Just put the measures in two different cards. The measures react to the slicer on the right.
Best
Darek
Hi,
Please see the link below containing the .pbix file:
https://drive.google.com/file/d/1KyNpW9IeJiSId3q_WkZQC0XkXYEwDSiW/view?usp=drivesdk
Well, Google does not let me access this file...
Best
Darek
- ao3527 years agoFrequent Visitor
Hi,
Sorry, I've granted access, are you able to download the file now?
- Anonymous7 years agoNot applicable
Yeah... I could now. I'll have a look. Bear with me.
Best
Darek
- Anonymous7 years agoNot 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 __resultHere's the result. Just put the measures in two different cards. The measures react to the slicer on the right.
Best
Darek
- ao3527 years agoFrequent Visitor
It works! Thank you :)