Forum Discussion

Bokchoy's avatar
Bokchoy
Icon for Helper II rankHelper II
4 years ago
Solved

look up value within temporary groupby table

Hi all,

 

I have a temp table that sums up total_charge, grouped by single_date. Now i want to return the date where the highest "sum all charge" was hit.

im having troubles with referencing the "Date" column within my temp table and im unable to complete the measure.

Please help, thanks.

 

Highest Daily_date=

var temp1 =  GROUPBY(Jobs, Jobs[job_date] , "Sum all Charge", SUMX(CURRENTGROUP(), Jobs[total_charge]))

var maxcharge_date = "                               " 

return maxcharge_date

 

Need Help
Show and Tell
  • ryan_mayu's avatar
    ryan_mayu
    4 years ago

    Bokchoy 

    is this what you want?

    maxdate = 
VAR tbl=SUMMARIZE(sample_jobs,sample_jobs[job_date],"charge",sum(sample_jobs[total_charge]))
VAR _max= maxx(tbl,[charge])
return maxx(FILTER(tbl,[charge]=_max),'sample_jobs'[job_date])

month = FORMAT([maxdate],"mmm")

6 Replies

    • ryan_mayu's avatar
      ryan_mayu
      Icon for Super User rankSuper User
      4 years ago

      Bokchoy 

      is this what you want?

      maxdate = 
VAR tbl=SUMMARIZE(sample_jobs,sample_jobs[job_date],"charge",sum(sample_jobs[total_charge]))
VAR _max= maxx(tbl,[charge])
return maxx(FILTER(tbl,[charge]=_max),'sample_jobs'[job_date])

month = FORMAT([maxdate],"mmm")

      • Bokchoy's avatar
        Bokchoy
        Icon for Helper II rankHelper II
        4 years ago

        Hi ryan,

        thanks for helping!

        i was able to get what i needed from your code, though i used the Groupby function instead of summarize. The return maxx line was that i was looking for. 

        again thanks heaps.

        Record Daily date = 

#maxdate = 
#VAR tbl=SUMMARIZE(jobs,Jobs[job_date],"charge",sum(jobs[total_charge]))
#VAR _max= maxx(tbl,[charge])
#return maxx(FILTER(tbl,[charge]=_max),Jobs[job_date])

var temp1 = GROUPBY(Jobs, Jobs[job_date] , "Sum all Charge", SUMX(CURRENTGROUP(), Jobs[total_charge]))
var High_Daily = MAXX(temp1, [Sum all Charge])
var date_ = MAXX(FILTER(temp1, [Sum all Charge] = High_Daily), Jobs[job_date])

return date_