Average line or measure card per category

[Freeseman]

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

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

[tamerj1 · Accepted solution]

Hi @Freeseman 
Attached is the updated file as requested

[Freeseman]

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

Thanks so much. Big Kudo to you.

[tamerj1]

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

[Freeseman]

@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

[tamerj1]

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

[Freeseman]

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 

[Freeseman]

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

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

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

[Freeseman]

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

You can try by the link to the file.

[amitchandak]

@Freeseman ,

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