cancel
Showing results 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

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.

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

1 ACCEPTED SOLUTION
Super User

Hi @Freeseman
Attached is the updated file as requested

10 REPLIES 10
Super User

Hi @Freeseman
Attached is the updated file as requested

Helper II

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

Thanks so much. Big Kudo to you.

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

@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

Super User

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

Helper II

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

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

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

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

Helper II

@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.

Super User

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

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

Helper II

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

You can try by the link to the file.

Super User

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

Announcements

#### 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 Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors