Re: Shaping Dates into categories

Freeseman · ‎08-09-2022

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 · ‎08-09-2022

Anybody have ideas:)

v-easonf-msft · ‎08-15-2022

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 · ‎08-15-2022

@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 · ‎08-16-2022

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 · ‎08-16-2022

@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 · ‎08-16-2022

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 · ‎08-16-2022

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 · ‎08-16-2022

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 · ‎08-17-2022

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