Forum Discussion

jopezzo's avatar
jopezzo
Icon for Helper I rankHelper I
7 years ago
Solved

How to filter a table based on a column's highest value

Hi,

 

I am trying to calculate the total quantity by Quality from a table called "Data", but filtering on the highest value in Period (in this case, 201906).  The purpose is to calculate the variance for each quality (highest period/lowest period).

 

In other words, I would like to get for each product Quantity [Period 201906] / Quantity [Period 201902]

 

Source file

 

I have tried different formulas but I'm struggling with the filter context.

 

Can anyone help me?

 

Thanks!

  • Anonymous's avatar
    Anonymous
    7 years ago
    Please use the same link as above.

    Best
    D.

8 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Create a DAX formula that goes something like this:

     

    Measure IsHighest = 
  VAR __table = SUMMARIZE(ALLSELECTED('Table'),[Quality], [Period],"__Quantity",SUM([Quantity]))
  VAR __max = MAXX(__table,[__Quantity])
  VAR __period = MAXX(FILTER(__table,[__Quantity] = __max),[Period])
RETURN
  IF(MAX([Period]) = __period,1,0)

    Then just fiilter on this being 1

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    Again, please resist the temptation to do it in DAX. Use Power Query because this is the right tool for the job.

    Best
    D.