Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Fresquito_14
Frequent Visitor

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. 


Fresquito_14_0-1640181203100.png

 

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


Fresquito_14_1-1640181227723.png

 

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 .

2 ACCEPTED SOLUTIONS
smpa01
Super User
Super User

@Fresquito_14  can you try the above?

 

smpa01_0-1640272985043.png

 

 

 

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
)

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

@Fresquito_14 please refer to the attached pbix

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

10 REPLIES 10
smpa01
Super User
Super User

@Fresquito_14  can you try the above?

 

smpa01_0-1640272985043.png

 

 

 

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
)

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Looks like it works. Thank you very much.

@Fresquito_14  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 ) )
)

 

 

smpa01_0-1640279761339.png

 

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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

@Fresquito_14 please refer to the attached pbix

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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

Thanks

PaulOlding
Solution Sage
Solution Sage

Hi @Fresquito_14 

 

Something like

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

 

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

Fresquito_14_0-1640193675620.png

Thank you in any case.

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

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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.