cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors