Forum Discussion

mattterriault's avatar
mattterriault
Frequent Visitor
5 years ago
Solved

Apply Filter Before Calculation

The formula I have below works but not when I do a filter. I am trying to get the lowest value in a group and assign a 1 to it and 0 when it is not the lowest value. Can I add a If filtered or someth...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi mattterriault ,

    Please create another new measure with below formula to replace the measure [Lowest Value] on the matrix:

    Measure =
SUMX (
    VALUES ( 'Table'[Distance] ),
    SUMX ( VALUES ( 'Table'[Distance] ), [Lowest Value] )
)

    Or you can refer the method to fix the incorrect total value problem:

    Power BI: Totals Incorrect

    Fixing Incorrect Measure Totals

    Why Your Total Is Incorrect In Power BI

    Best Regards
    Community Support Team _ Rena Ruan
    If this post helps, then please consider Accept it as the solution to help the other members find it more.

DataInsights's avatar
DataInsights
Super User
5 years ago

mattterriault,

 

Try this measure. The reason that your calculated column did not work properly is that calculated columns are not affected by filter context.

 

Lowest Value =
VAR vScore =
    MAX ( Table[Score] )
VAR vGroupMinValue =
    CALCULATE ( MIN ( Table[Score] ), ALLSELECTED ( Table[Company] ) )
VAR vResult =
    IF ( vScore = vGroupMinValue, 1, 0 )
RETURN
    vResult

 

 

 

mattterriault's avatar
mattterriault
Frequent Visitor
5 years ago

Thank you for responding. This works but it does not roll it up like the column calculation. The ultimate goal is to say " out of what is selected, how many times was company A the lowest, how many times was company B the lowest, etc....". I would like to not even have distance and size in the view, just the sum of the calculation you provided. Thanks again, I know I didnt clarify all that at the beginning.