Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Aggregate functions

Hi Community,

 

I am new to Power BI and hence I'd need help with creating DAX Formulas.

I have two columns 1) Week (the date values are in 7 days interval) 2)  Quantity

I would need the Minimum, Maximum and Average of Quantity based on the week column(Precisely past 6 weeks from the current week) - I will be using these aggregates on the Line values in a chart projecting over the weeks. My question is:

1) Do I create a Measure or a new column

2)  DAX query or the syntax would be helpful.

 

Thanks 🙂

1 Reply

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi Anonymous 

    try a measures

    Minimum = CALCULATE(MIN('Table'[Quantity]), ALLEXCEPT('Table', 'Table'[Week]))
    Maximum = CALCULATE(MAX('Table'[Quantity]), ALLEXCEPT('Table', 'Table'[Week]))
    Avg = CALCULATE(AVERAGE('Table'[Quantity]), ALLEXCEPT('Table', 'Table'[Week]))​

     where "Table' - your table name, [Week] - name of column with week

     

    do not hesitate to give a kudo to useful posts and mark solutions as solution