Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

All Time High and Low based on multiple Fields and Date

Hello all:

 

I am trying to figure out how to get an ATH measure based on a couple of fields. BAsiclaly the data is structured like below (with many other columns, but not of concern), and I'd like to get measures that produce the ATH or ATL. 

 

I'll likely have some filters going on for Set, in case that makes a difference.

 

EDIT: as additional info: What I'm looking for it to do is compare the Totals for each date and then return which of those was highest. It would therefor have to SUM up the totals by Date for all different Variety (per each date) and then return whichever one was highest.

 

So would SUM Green, Red, and Blue for 1/5/2022 and compare to the SUM of Green, Red, and Blue for 2/5/2022.

 

SetVarietyDateTotal
Set 1Green1/5/2022 $  100.00
Set 1Green2/5/2022 $  110.00
Set 1Green3/5/2022 $    90.00
Set 1Red1/5/2022 $    20.00
Set 1Red2/5/2022 $    10.00
Set 1Red3/5/2022 $       5.00
Set 1Blue1/5/2022 $  800.00
Set 1Blue2/5/2022 $  950.00
Set 1Blue3/5/2022 $  700.00

 

Set 1 ATH $  1,070.00
Set 1 ATH Date2/5/2022
  
Set 1 ATL $     795.00
Set 1 ATL3/5/2022

 

Thank you to anyone that gives advice, I appreciate it

Need Help

3 Replies

  • SpartaBI's avatar
    SpartaBI
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Anonymous maybe more info is required in case total is a column in your table then try this:
    Set 1 ATH = CALCULATE(MAX('Table'[Total]), REMOVEFILTER('Table'), VALUES('Table'[Set])
    Set 1 ATL = CALCULATE(MIN('Table'[Total]), REMOVEFILTER('Table'), VALUES('Table'[Set])


    In case it answered your question, please accept the solution to help other members find it. Appreciate Your Kudos.

    Showcase Report – Contoso By SpartaBI


    Visit SpartaBI website   Visit SpartaBI Linkdin   Visit SpartaBI Facebook

    SpartaBI Logo

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      SpartaBI 

       

      Thank you for the response. I think I can provide some extra information:

       

      What I'm looking for it to do is compare the Totals for each date and then return which of those was highest.

       

      It would therefor have to SUM up the totals by Date for all different Variety (per each date) and then return whichever one was highest.

       

      So would SUM Green, Red, and Blue for 1/5/2022 and compare to the SUM of Green, Red, and Blue for 2/5/2022.