Forum Discussion

jeo's avatar
jeo
Regular Visitor
2 years ago

How do i display monthly and yearly data in one graph?

Hi all,   I am trying to put to data into one graph. One data is to show the average number of work that shld be done in a month of a year and the other data shows the count of work per month. Both...
Need Help
Tutorial Requests
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi jeo ,

It is not possible to achieve this in Power BI.

Because the display of each polyline will be affected by everything in the x-axis. You can't have a polyline showing only one value based on Year and another straight line showing 12 values based on Month.
Maybe I can give you a workaround. Assuming that you already have a measure available to calculate Suggested WI(s), you can add another measure like this:

Measure = 
IF(
    MAX('Table'[Month]) = 6,
    [Suggest WI(s)]
)

The effect of putting this measure into a line chart is as shown in the figure below, but this method can only display a point and cannot connect these points into a line.


Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

jeo's avatar
jeo
Regular Visitor
2 years ago

Hi Anonymous ,

This seems like the best idea i got. But may i know what do i put for ('Table'[Month]).

Regards,
Jeo

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi jeo ,

    I see that there are years and months in the screenshot you provided, but I'm not sure what the actual data looks like, so in my example I chose June (because June is near the middle of the year, and all my years contain January-December), and you can choose your own months that are near the middle of the year to display the data.
    For example:

    Measure = 
IF(
    (MAX('Table'[Year]) = 2024 && MAX('Table'[Month]) = "July") || (MAX('Table'[Year]) = 2025 && MAX('Table'[Month]) = "June"),
    [Suggest WI(s)]
)


    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • jeo's avatar
      jeo
      Regular Visitor
      2 years ago

      Hi Anonymous ,

       

      But my data is dynamic and not all the months will be included. And my Month and Year data type is not separated, they in a date-type data column.

      Best Regards,
      Jeo

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi jeo ,

        If your dates are dynamic, it's going to be a pain in the ass and you'll probably need to manually specify the month each year. Other than that there is no other way.
        If your data type is date-time, you can change 'Table'[Year] to 'Table'[Date]. [Year], change 'Table'[Month] to 'Table'[Date]. [Month]

        Best Regards,
        Dino Tao