Average Accumulated as a measure

[rmcneish]

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.

[rmcneish]

My buddies.

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

I attach my file in One Drive.

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

Grettings.

@Anonymous

[Anonymous]

Hi @rmcneish,

Do you need a measure for average hours per order?

[rmcneish]

Hi @Anonymous

Yes, respecting the order.

[Anonymous]

Hi @rmcneish,

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

[rmcneish]

Hi @Anonymous

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

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

[Anonymous]

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. 

[rmcneish]

Hi @Anonymous

The reuslt is the same.

}

Consult the attached file .pbi please.

https://tasaomega-my.sharepoint.com/:u:/g/personal/rnina_tasa_com_pe/EdkS5qs_VTVIkxHA1HV4hWABFNH1CLNNgebCfSg6QV4rfg?e=FATpQK

Hi @Vvelarde

[Anonymous]

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?

[rmcneish]

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

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

Sorry I'm annoying.

[Anonymous]

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

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

[Vvelarde · 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

[rmcneish]

Hola @VvelardeMe sirvió de mucho.

Gracias.