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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jda
Frequent Visitor

Line and stacked column chart, how to display all data

Hello,

 

I'm trying to create a report that compares our actual sales to our budgeted sales.

The total budget per month is plotted as a line in the line and column chart, while the actual sales of the different products are stacked columns.

 

What I would like to do, is to have the budget for all the months of the year present in the chart as a line, while the columns of the actual sales would show up as the sales are made.

 

Unfortunately for the months in the future, where there are no sales registered yet, the budget line disappears as well. I have tried use the "Show items with no data" on the shared axis, however that only makes the months without sales show up, not the budget for those months.

 

Is there a way to make this chart show the line values although there are not any corresponding column values?

1 ACCEPTED SOLUTION
jda
Frequent Visitor

Now I got it to work.

 

I updated to the latest verison of Power BI, and started from scratch by loading in the tables and creating the relationships.

 

Not sure what is different, but now it works:)

View solution in original post

7 REPLIES 7
SamLester
Employee
Employee

 By default, I'm able to display the budget line even though there are no values in my sales column for upcoming years. Can you provide a screen shot that includes the values you're using for the visualization parameters? 

 

 

 

BarLine.PNG

 

Thanks,
Sam Lester (MSFT)

jda
Frequent Visitor

Line&ColumnChart.jpg

 

Here is one example of what it looks like. Since there are no sales past August, the budgets for the remaining months don't show up.

ankitpatira
Community Champion
Community Champion

@jda just to ensure, i can see you have two filters of that you have New Sale set to New Contract. are you sure that is not stopping budget to show up ?

 

Hello Ankitpatira,

 

I'm not sure, but that would be a little strange. The New Sale filter is a filter on a table which contains the sales data, while the budgets are part of a different table which does not have that column. So the New Sale filter shouldn’t really apply for the budget table?

 

If I remove the New Sale filter, the budget for the rest of the year does show up, but I think that is because there are registered sales for the remaining months of the year, but they are not "New Contract". I have the same problem if I filter different columns (for example Region), if there are no sales for that period and region.

 

As sdjensen showed, it is certainly possible to achieve what I am trying to do. Maybe I’ll have to take a closer look at the filters or table relations that I use, to figure out the problem. I’m fairly new to Power BI

 

 

jda
Frequent Visitor

Now I got it to work.

 

I updated to the latest verison of Power BI, and started from scratch by loading in the tables and creating the relationships.

 

Not sure what is different, but now it works:)

What happens is you change the visual to a column chart? I have seen some strange behaviours with combined line and column chart in the latest release of PBI Desktop.

/sdjensen
jda
Frequent Visitor

Same issue unfortunately.

The budget past August is missing.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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