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
Freeseman
Helper II
Helper II

Average line or measure card per category

Hi All,

 

I have just about finished this dash but struggling with averages.

I would like to have an average card or line per category but my meassure is not working well.

 

I need to sum each categoy total production and divide it by the number of hours.

Eg... in BLUE is evening shift change. 

hour 18: 7191

hour 19: 8796

hour 20: 8957

hour 21: 9056

hour 22: 9178

SUM is : 43178/5 = average of 8635

 

However my average line is showing 97.

Freeseman_1-1661230998358.png

Here is my measure:

Evening Shift Change = if(MAX('PRODUCTION'[TIMESTAMP (groups)])="1. Evening Shift Change",CALCULATE(AVERAGE('PRODUCTION'[PRODUCTION]),FILTER(ALL('PRODUCTION'),'PRODUCTION'[TIMESTAMP (groups)]="1. Evening Shift Change")))

Freeseman_0-1661230612967.png

https://onedrive.live.com/?authkey=%21AFjqwQTgXMKiFsw&cid=AD44D02CDB02CE2F&id=AD44D02CDB02CE2F%21114...

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @Freeseman 
Attached is the updated file as requested

1.png2.png

View solution in original post

10 REPLIES 10
tamerj1
Super User
Super User

Hi @Freeseman 
Attached is the updated file as requested

1.png2.png

@tamerj1 You have been the solution to what i have been wanting to achieve.

Thanks so much. Big Kudo to you.

tamerj1
Super User
Super User

Hi @Freeseman 
Attached your sample file with the solution 

Evening Shift Change = 
VAR Result = 
IF (
    MAX ( 'PRODUCTION'[TIMESTAMP (groups)] ) = "1. Evening Shift change",
    VAR TotalSum =
        CALCULATE (
            SUM ( 'PRODUCTION'[PRODUCTION] ),
            'PRODUCTION'[TIMESTAMP (groups)] = "1. Evening Shift Change",
            ALLSELECTED ( 'PRODUCTION' )
        )
    VAR TotalCount =
        CALCULATE ( 
            COUNTROWS ( VALUES ( PRODUCTION[TIMESTAMP] ) ), 
            'PRODUCTION'[TIMESTAMP (groups)] = "1. Evening Shift Change", 
            ALLSELECTED ( PRODUCTION ) 
        )
    RETURN
        DIVIDE ( TotalSum, TotalCount )
)
RETURN
    Result

@tamerj1 I guess what i was trying to say is, If you drag the slider it should always stay within daily average rates. Like sum of production / shift date (hours) if it increases.

If i drag the slider, the production goes higher and the averages remain good but i need this average to be per hour per shift date... the Y axis must always be around 12k - 15k max something to do with the average over a long period however for the day looks perfect

Hi @Freeseman 
To be honest, I did not fully understand what you are trying to say. Would you please present some examples that might help me better understand your requirement. Thank you

If i do the drop down slicer per day the averages make sense for the total production.

Freeseman_1-1661242290665.png

If i do it over more than 1 day eg: 3 months it is incorrect as it jumps to millions.

Freeseman_3-1661243111574.png

 

Here is a dash i am trying to replicate and it is done over 3 months.

Freeseman_2-1661242928894.png

It is as if we needed to get average tons by period of time

Over a long period of time the average tons per category "SHIFT" should always be between 4k and 9K does this make sense? @tamerj1 

@tamerj1 YES!!!!!!

Thank you, this is exactly what i am needing. this is perfect however is it possible to keep the average at 8k?

what i mean is... The average if you drage the date slider should be the average of the total production in those 5 hours divide by 5.

 

amitchandak
Super User
Super User

@Freeseman , Using timestamp or hour of timestamp, create a measure like

 

calculate(averagex(values(PRODUCTION[TImeStamp]), [Evening Shift Change]), allselected())

Thanks @amitchandak but this gave me an average of all. I need per category.

You can try by the link to the file.

@Freeseman ,

 

calculate(averagex(values(PRODUCTION[TImeStamp]), [Evening Shift Change]), Filter(allselected(PRODUCTION), PRODUCTION[Category] = max(PRODUCTION[Category])  ) )

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.