Shaping Dates into categories

[Freeseman]

Hi All,

I am trying to structure my X axis by hour but in a specific order. So by default Power BI would arrange my hours from 0 - 23

or it would kind of just mix them up as below.

The result i am trying to acheive is like this:

Please help me in the right direction to replicate the second visual

[Freeseman]

Anybody have ideas:)

[v-easonf-msft]

Hi, @Freeseman 

You need to enter a custom table and then sort Column ‘Category’ by Column 'Sort column'.

After you establish a relationship between the custom table and the fact table, you can replace the existing fields with the new fields like below:

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Freeseman]

@v-easonf-msft Thank you for the advice. Please see https://community.powerbi.com/t5/Desktop/Average-per-Category-Please-help/m-p/2702026#M945089

I managed to get this far. Is the next step possible?

[v-easonf-msft]

Hi, @Freeseman 

Try to use 'Line and clustered column chart' to replace 'clustered column chart' so that  you can add custom line chart as below:

Average of Part1 = if(MAX('Fact Table'[Category (groups)])="Part1",CALCULATE(AVERAGE('Fact Table'[Value]),FILTER(ALL('Fact Table'),'Fact Table'[Category (groups)]="Part1")))

Average of Part2 = if(MAX('Fact Table'[Category (groups)])="Part2",CALCULATE(AVERAGE('Fact Table'[Value]),FILTER(ALL('Fact Table'),'Fact Table'[Category (groups)]="Part2")))

......

Best Regards,
Community Support Team _ Eason

[Freeseman]

@v-easonf-msft 

This is extremely helpful.

I need the average of the SUM of production per group. I tried this:

NS Lunch = if(MAX('PRODUCTION'[TIMESTAMP (groups)])= "2. NS Lunch",CALCULATE(AVERAGE('PRODUCTION'[PRODUCTION]),FILTER(ALL('PRODUCTION'),'PRODUCTION'[TIMESTAMP (groups)]= "2. NS Lunch")))

My Average lines do not look good. I need to work something out.

[Freeseman]

I think it has something to do with the way its grouping the times vs reading the production value?

Production is SUM over multiple timestamps because there are many production values from different machines in the hours. SO i need the sum of the total hour groups production which i believe is possible but the meassure needs to change calculation?

Its making all average line 97.xx

[v-easonf-msft]

Hi， @Freeseman 

Please convert the chart into a table visual, which will be easy to find the problem.

Can you share a copy of the sample data?

Best Regards,
Community Support Team _ Eason

[Freeseman]

Thank you.

The data is a lot to share as i have many other tabes from other DB tables.

The way you explain to me is very good and helps me a lot. I just need to fix meassure calculation?

[v-easonf-msft]

Hi, @Freeseman 

I suspect this has something to do with the date slicers. 

Is 'Shift Date' a field in a separate calendar table?

Will the result of the current measure "Evening Shift Change" change with the value of slicer 'shift_date'?

After you clear the filter criteria for the date slicer, is the calculation result correct?

NS Lunch =
IF (
    MAX ( 'PRODUCTION'[TIMESTAMP (groups)] ) = "2. NS Lunch",
    CALCULATE (
        AVERAGE ( 'PRODUCTION'[PRODUCTION] ),
        FILTER (
            ALL ( 'PRODUCTION' ),
            'PRODUCTION'[TIMESTAMP (groups)] = "2. NS Lunch"
                && 'PRODUCTION'[Shift Date] IN VALUES ( 'PRODUCTION'[Shift Date] )
        )
    )
)

Best Regards,
Community Support Team _ Eason

[Freeseman]

@v-easonf-msft 

No Idea where it is getting 96.02 from

[Freeseman]

Hi @v-easonf-msft 

I tried to upload the file for you but its not allowing me.

I tried your new measure but same issue... i am not sure where it is taking this average from because i need the average of the 2 totals (0 and 23)

[v-easonf-msft]

Hi， @Freeseman 

If possible, upload a sample file to cloud storage and share the link here.

Best Regards,
Community Support Team _ Eason

[Freeseman]

@v-easonf-msft Please look. 🙂

I need help caterorising average per time category per day but also drag slider and need average over time.

https://1drv.ms/u/s!Ai_OAtss0EStcVjqwQTgXMKiFsw?e=auxUfP

[v-easonf-msft]

Hi， @Freeseman 

Sorry for later reply. The link seems to have expired, can you share it again?

Best Regards,
Community Support Team _ Eason

[Freeseman]

@v-easonf-msft I inbox you.

[Freeseman]

Hi @v-easonf-msft , Struggle with company procedure.

Does this help

I have time stamps but the average is calculated per row and not per category?

[Freeseman]

Wow, i am amazed. 🙂 Thank you. Let me try this.

[Freeseman]

I need the dates ordered from 18,19,20,21,22 then 23,0 Then 1,2,3,4 Then 5,6,7,8 Then 9,10,11 Then 12,13 Then 14,15,16,17

It is from 6pm to 5pm over a 24h