Forum Discussion

Freeseman's avatar
Freeseman
Icon for Helper II rankHelper II
4 years ago

Shaping Dates into categories

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 re...
Need Help
v-easonf-msft's avatar
v-easonf-msft
Icon for Community Support rankCommunity Support
4 years ago

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.

 

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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's avatar
      Freeseman
      Icon for Helper II rankHelper II
      4 years ago

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

    • Freeseman's avatar
      Freeseman
      Icon for Helper II rankHelper II
      4 years ago

      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's avatar
        Freeseman
        Icon for Helper II rankHelper II
        4 years ago

        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