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 has been the Lead Time. For each item there is a Lead Time. 


 

Each item must be categorized in a product catalog. Then the metricts will be shown by product:


 

The only metric that I havent been able to calculate with Power BI desktop is LT_average_max. I want to get the value of the average of the month with the highest (or lowest) average. At the moment I am using and aux matrix summarizing the averages by product and by month, and them I've to copy all manually to anothe table for the presentations (very arcaic).

 

Taking in account my researchs, I guess that the optimal way to get this value is by using a measure. But at the moment I can only write very simple functions and I'm not ready to solve this problem. 

 

  • Is this measure feasible or should I look for a work around?
  • In case the measure is feasible, could somebody help to formulte it?

 

Thank you in advanced for trying to help me.

 

P.D. This is my first post. If you have found points on which I can improve, I would be grateful if you could point them out to me .

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

10 Replies

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    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
)

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Looks like it works. Thank you very much.

      • 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 ) )
)

         

         

         

         

         

         

  • 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.