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

[jeo]

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 data share the same year but the data that shows the work per month i want it to only show marker for the year and not month as the value for each month is the same. 

<This is the graph right now

This is the DAX expression to count the average ork to do a month^

Regards,
Jeo

[v-junyant-msft]

Hi @jeo ,

Please check whether this is what you want?

I don't know what your dataset looks like, but based on the DAX you provided your dataset will probably contain a larger number of rows, to save time I created a sample data to give you an example:

You are using COUNTROWS, my side is using SUM, please modify according to your specific situation.
I use this DAX to create a masure to calculate the Suggested WI(s), I could have just used Average in my dataset, but considering that you might not be able to use it, I went with the total divided by 12 instead:

Suggest WI(s) = 
DIVIDE(
    CALCULATE(
        SUM('Table'[Suggest WI]),
        ALLEXCEPT('Table', 'Table'[Year])
    ),
    12
)

And build the visual:

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]

Hi @v-junyant-msft ,

The suggested WI(s) was not provided in my excel but it was a measure that i made to count the total number of WIs in a year and divide it by 12 to find the average. The Yearselect is a measure i made to so that the expression will count the WIs in a certain date. I was thinking if it is possible to link the average WI(s) to each year instead of month so it the graph will look something like this. 

Something like the one below just that the count of Work Title will be in months while the Suggested WI(s) will remain in that marker format.

Regards,
Jeo

[v-junyant-msft]

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]

Hi @v-junyant-msft ,

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

Regards,
Jeo

[v-junyant-msft]

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]

Hi @v-junyant-msft ,

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

[v-junyant-msft]

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

[jeo]

Hi @v-junyant-msft ,

Alright. Thank you very much.

Best Regards,
Jeo

[jeo]

Hi @v-junyant-msft ,

Is there a way to take the suggestedWI(s) and assign them to the months in the excel so it can display a line?

Best Regards,
Jeo

[v-junyant-msft]

Hi @jeo ,

Sorry for the late reply, but I didn't understand what you meant?

Best Regards,
Dino Tao

[jeo]

Hi @v-junyant-msft ,

All good! I meant if it is possible to assign the suggestedWI(s) value of each year to the dynamic months in each year so the line graph will show a flat line over each year.

Best Regards,
Jeo

[v-junyant-msft]

Hi @jeo ,

Are you saying that it's like the effect provided in my first reply that shows a straight line each year instead of a point? This could theoretically be achieved using DAX, but I would need to know what your data looks like before I could attempt it.

Best Regards,
Dino Tao