Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Measure: return the max average(by months)

Hi, I've started with Power BI a week ago. I found that was the perfect tool for what i was asked for. I've been chosen to get the time measures of the processes of my company. The metric chosen ha...
Measures
Power BI Desktop
Power Query
  • smpa01's avatar
    smpa01
    4 years ago

    Anonymous  can you try the above?

     

     

     

     

    avgByProdMaxYear = 
CALCULATE (
    AVERAGE ( 'Sample'[LT] ),
    VAR _base =
        GROUPBY (
            'Sample',
            'Sample'[Product],
            'Sample'[Year],
            "avg", AVERAGEX ( CURRENTGROUP (), 'Sample'[LT] )
        )
    VAR _rank =
        SUMMARIZE (
            FILTER (
                ADDCOLUMNS (
                    _base,
                    "rank", RANKX ( FILTER ( _base, [Product] = EARLIER ( [Product] ) ), [avg],, DESC )
                ),
                [rank] = 1
            ),
            'Sample'[Product],
            'Sample'[Year]
        )
    RETURN
        _rank
)

     

     

  • smpa01's avatar
    smpa01
    4 years ago

    Anonymous please refer to the attached pbix

PaulOlding's avatar
PaulOlding
Icon for Solution Sage rankSolution Sage
4 years ago

Hi Anonymous 

 

Something like

LT_Average_max = 
MAXX(
    VALUES(Table[month]),
    AVERAGE(Table[Lead Time])
)

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi PaulOlding.

This solution gives me the same solution as the annual average. The measure that I am looking for should calculate the LT for each month and provide the maximum value. In the table attached, LT_Average_min = 18,92 (I need the lowest btw, but it does not affect).  

Thank you in any case.

  • PaulOlding's avatar
    PaulOlding
    Icon for Solution Sage rankSolution Sage
    4 years ago

    I think to help any further I would need some example data and the result you're expecting from that.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I've made a sample in which three tables are shown. 

      • Sample data
      • Summary
      • Summary_by_Year_Product (Pivot table)

      You can access the table through this link: data example.xlsx

      The pasword is:

      Power_BI