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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
rmcneish
Helper I
Helper I

Average Accumulated as a measure

Hello friends.

I need to calculate the average acomulated as a measure to be able to use it in a pivoting matrix (measure), I want to have it as a measure and not creating a calculated column, please need help.

 

Best greetings.

 

1.JPG2.JPG

1 ACCEPTED SOLUTION

@rmcneish

 

Hi, try with this measure:

 

Measure =
VAR THEORDER =
    SELECTEDVALUE ( ESPERA[ORDER] )
RETURN
    AVERAGEX (
        SUMMARIZE (
            FILTER (
                CROSSJOIN ( ALLSELECTED ( ESPERA[ORDER] ); VALUES ( ESPERA[PLANT] ) );
                ESPERA[ORDER] <= THEORDER
            );
            "AVG"; AVERAGE ( ESPERA[HOURS] )
        );
        [AVG]
    )

Regards

 

Victor

Lima - Peru




Lima - Peru

View solution in original post

12 REPLIES 12
rmcneish
Helper I
Helper I

My buddies.

I want to create a measure to use it in a matrix or different line charts. Smiley Sad3.JPG

I attach my file in One Drive.

https://tasaomega-my.sharepoint.com/:u:/g/personal/rnina_tasa_com_pe/EdkS5qs_VTVIkxHA1HV4hWABFNH1CLN...

 

Grettings.

 

@Anonymous

Anonymous
Not applicable

Hi @rmcneish,

 

Do you need a measure for average hours per order?

Hi @Anonymous

Yes, respecting the order.

 

Anonymous
Not applicable

Hi @rmcneish,

 

Could you try Calculate(average(Table[Hours]), Groupby(Table, Table[Order]))

Hi @Anonymous

I try the solution you gave me but the average is the same.

Accumulated = CALCULATE(AVERAGE(ESPERA[HOURS]),GROUPBY(ESPERA,ESPERA[ORDER]))

 

4.JPG

Anonymous
Not applicable

Hi @rmcneish,

 

I am a little confused. If you wish to see the output as in your first screenshot, you need to remove "Plant" from the matrix. 

Anonymous
Not applicable

Hi @rmcneish,

 

Try Accumulated = ROUNDUP(CALCULATE(AVERAGE(ESPERA[HOURS]),GROUPBY(ESPERA,ESPERA[ORDER])),1)

 

and then in the modelling tab, change the format to Decimal number and decimal points to 1.

 

Is this what you need?

Hi @Anonymous

Thnks for your great support and i valur so much your help, but i wish calculate the accumulated average in any selection of my filters ("#BOARD" and "PLANT").

02.JPG

 

In the next tablle I show the real calculated by all #BOATS and all  PLANTS

01.png

 

Sorry I'm annoying.

Anonymous
Not applicable

Oh I think I get it now 🙂 I think you would have to a table only with order ids and accumulated hours.

 

Table = DISTINCT(ESPERA[ORDER])

 

Then create a measure for accumulated hours

 

Accumulated Hours = Calculate(AVERAGE(ESPERA[HOURS]), filter(ESPERA, ESPERA[ORDER] = 'Table'[ORDER]))

 

Accumulated hours.PNG

 

 

You can also join this table with ESPERA table using order id as primary key.

 

@rmcneish

 

Hi, try with this measure:

 

Measure =
VAR THEORDER =
    SELECTEDVALUE ( ESPERA[ORDER] )
RETURN
    AVERAGEX (
        SUMMARIZE (
            FILTER (
                CROSSJOIN ( ALLSELECTED ( ESPERA[ORDER] ); VALUES ( ESPERA[PLANT] ) );
                ESPERA[ORDER] <= THEORDER
            );
            "AVG"; AVERAGE ( ESPERA[HOURS] )
        );
        [AVG]
    )

Regards

 

Victor

Lima - Peru




Lima - Peru

Hola @VvelardeMe sirvió de mucho.

Gracias.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Top Solution Authors