question of line and stacked column chart.

[FD]

Hi, there,

I face issue of getting the right chart when doing line and stacked column chart.

I have 2 data tables. One table has product sale data.  2nd table has factory output data.

I am tryinng to use LINE and STACKED column chart to show info of these 2 tables.

The issue is i always could only get 1st set of data tabluated as per the month series but the 2nd set of data will be sum up in 1 year value and show total sum value on each month series.

the sum up of 1 year value was wrong.

I hope to see the actual value on that month for product sale & factory output respectively.   

Below is wrong output i got with sum of the 1 yr forecast. The factory output data is correct in the chart., follow the month.

This is another chart if i change the date selection for the shared axis.

the 1 year sum value goes to the factory output.

The forecast is correctly placed to each month. 

I hope to get the right chart to have both forecast and factory out data follow the month axis. Do not add sum value into the chart.

Here is the link to get my powerbi working file. link to download the powerbi work file. 

Please feel free to take a look and comment.

Hope the experts in the community can help me.

[amitchandak · Accepted solution]

@FD , Create a date table and join date from both the tables to join with it and use date from date table.

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[v-robertq-msft · Accepted solution]

Hi, @FD 

According to your sample data in your pbix file, I found that you can use merge query to solve this problem, you can follow my steps:

1. Go to Power query editor, click this:

2. Set the query like this(Hold on “Ctrl to select two columns”):

3. Get the table ‘Merge1’, then expand the table, like this:

4. Change the columns in the Slicer and line and stacked column chart(From table ‘Sheet1’,’Sheet3’ to table ‘Merge1’), like this:

And I guess this is what you want.

You can download my test pbix file here

If this result is not what you want, you can post your expected result so that we can help you better.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

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

[amitchandak · Accepted solution]

@FD , Create a date table and join date from both the tables to join with it and use date from date table.

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.