Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jeo
Regular Visitor

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 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. 

Screenshot 2024-05-23 095622.png<This is the graph right now

jeo_0-1716434002043.png

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

 

Regards,
Jeo

12 REPLIES 12
v-junyant-msft
Community Support
Community Support

Hi @jeo ,

Please check whether this is what you want?

vjunyantmsft_0-1716516698819.png

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:

vjunyantmsft_1-1716516781849.png

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:

vjunyantmsft_2-1716516889621.png


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.

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. 

Screenshot 2024-05-24 084020.png

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.

jeo_0-1716520493588.png

 

 

Regards,
Jeo





Hi @jeo ,

It is not possible to achieve this in Power BI.

vjunyantmsft_1-1716527038106.png

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.

vjunyantmsft_0-1716526991127.png


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.

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

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.

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

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

Hi @v-junyant-msft ,

Alright. Thank you very much.

Best Regards,
Jeo

jeo
Regular Visitor

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

Hi @jeo ,

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

Best Regards,
Dino Tao

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

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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Find out what's new and trending in the Fabric Community.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.