Forum Discussion

cferv_77's avatar
cferv_77
Icon for Helper I rankHelper I
3 years ago
Solved

Create a weekly measure for average, min, max after grouping

Hello, I am trying to create a weekly average, min, and max measure for the number of units delivered for every week (building a line chart) for this dataset containing (Location, Week #, Date, Deliv...
Need Help
  • cferv_77's avatar
    cferv_77
    3 years ago

    I found the answer to this by using MINX, MAXX, AVERAGEX and inputting the summary table in the function. 


    Example: 
    VAR summary_table = FILTER(   SUMMARIZE(‘Delivery Info’, ‘Delivery Info’[Location],’Delivery Info’[Year] ,  ’Delivery Info’[Date],  ’Delivery Info’[Week num],  "sum",  SUM(‘Delivery Info’[# Units]) )
    VAR week_avg = AVERAGEX( summary_table, [sum] ) 
    RETURN
    week_avg

     

    And then for month, same process but switched out the [Week num] for the [Month #] in the summary table: 
    FILTER(   SUMMARIZE(‘Delivery Info’, ‘Delivery Info’[Location],’Delivery Info’[Year] ,  ’Delivery Info’[Date],  ’Delivery Info’[Month #],  "sum",  SUM(‘Delivery Info’[# Units]) )

cferv_77's avatar
cferv_77
Icon for Helper I rankHelper I
3 years ago

I found the answer to this by using MINX, MAXX, AVERAGEX and inputting the summary table in the function. 


Example: 
VAR summary_table = FILTER(   SUMMARIZE(‘Delivery Info’, ‘Delivery Info’[Location],’Delivery Info’[Year] ,  ’Delivery Info’[Date],  ’Delivery Info’[Week num],  "sum",  SUM(‘Delivery Info’[# Units]) )
VAR week_avg = AVERAGEX( summary_table, [sum] ) 
RETURN
week_avg

 

And then for month, same process but switched out the [Week num] for the [Month #] in the summary table: 
FILTER(   SUMMARIZE(‘Delivery Info’, ‘Delivery Info’[Location],’Delivery Info’[Year] ,  ’Delivery Info’[Date],  ’Delivery Info’[Month #],  "sum",  SUM(‘Delivery Info’[# Units]) )