Forum Discussion

MiroslawW's avatar
MiroslawW
Frequent Visitor
7 years ago
Solved

Summing only max value from a day.

Hi,

I have got problem to make a pivot table that summerize maximum hours from a day schedule (I have copied only piece of it):

 

START DATEEND DATENAMETRAININGTYPEHOURSINSTRUCTORRESULT
21/01/201923/01/2019NAME1  22.00MWPASS
21/01/201921/01/2019NAME2  6.00NBPASS
21/01/201922/01/2019NAME3  6.00NBPASS
23/01/201923/01/2019NAME4  4.50NBPASS

 

I want to make a pivot table that summarize hours by instructor but i dont know how to make a measure in a DAX. 

 

My pivot table looks like this:

 

Sum of HOURSColumn Labels 
Row LabelsMWNBGrand Total
2019157233.5391
Qtr1157233.5391
Grand Total157233.5391

 

I want to sum only max value from 21/01/2019 of instructor NB. Is there any measure that I could use?

Thank You,

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    7 years ago

    You should be able to do something like:

     

    Measure 6 = 
VAR __date = MAX([START DATE])
VAR __instructor = MAX([INSTRUCTOR])
VAR __table = SUMMARIZE('Table8',[START DATE],[INSTRUCTOR],"__hours",MAX([HOURS]))
RETURN
MAXX(__table,[START DATE]=__date && [INSTRUCTOR]=__instructor)

1 Reply

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

    You should be able to do something like:

     

    Measure 6 = 
VAR __date = MAX([START DATE])
VAR __instructor = MAX([INSTRUCTOR])
VAR __table = SUMMARIZE('Table8',[START DATE],[INSTRUCTOR],"__hours",MAX([HOURS]))
RETURN
MAXX(__table,[START DATE]=__date && [INSTRUCTOR]=__instructor)