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

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
4 years ago

Anonymous  while this works, I want to leave you with a more elegant 1 line measure. Pbix is attached.

 

_top1Average =
CALCULATE (
    [average],
    KEEPFILTERS ( TOPN ( 1, ALLSELECTED ( Sample_data[Year] ), [average], DESC ) )
)

 

 

 

 

 

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thank you but this second solution does not work for me. I will try it later when I'll get a bertter understanding of DAX and M. 

The error that I get is that there is no column [average].

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Anonymous please refer to the attached pbix

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Now it works, I didn't know that [average] were a measure. This shows exactly what I'was looking for.

      Thanks