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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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
Europe Fabric Conference

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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