Line & Stacked Column Chart, with more than 1 line shown

[andreako]

Hello,

I'm trying to create a report that compares monthly production plan for a yearly basis, with total sales forecast and total inventory stock balance for every month.  The total sales forecast and total balance is plotted in the line, and the column is the production plan for every part code.

Below is the current chart plotted using Excel for more information which need to achieve :

Current Source Data in Excel

Based on the above source data, the chart is plotted as following:-

Current Chart using Ms Excel

Using PowerBI Desktop, import raw data from excel and after some massage, able to prepare source table as following:-

Source Data @ PowerBI Desktop

Due to the raw data do not have period indicated, hence prepare another set of data to indicate mth01 is Jan2018. This is to ensure everytime i can just maintain the correct period in the excel and information can be refreshed easily.

Period

My current chart @ Power BI, not really reflecting what i need. I am not sure is it because the source data preparation issue or it is not possible to replace my current chart using power BI.

Chart @ PowerBI

Problem of Chart in Power BI

1. How to change those different partcode under one column, from column mth01 to column mth12?

2. How the total sales and total balance should be presented in table, so that i will only get 2 lines? ( currently, if choose all the period, all the 12 lines will be shown)

3. How to change mth01 to Jan'18 based on the period file i maintain?

I have upload my pbix  here, for you to get better understanding on how those data structure looks like.  

Any help or advice is very much appreciate here!

Thank you in advance.

Andrea Ko

[v-yuta-msft · Accepted solution]

Hi andreako,

Can't achieve your raw data so i input a simpler sample table like below.

 

To achieve your requirement, you can click Query Editors->Transform->Click on Mth01~Mth02->Unpivot Columns, the result will be like below:

  

After applied&close, you can create a stack&line chart, drag [Month] column to Shared axis field, [Part Code] column to column series field, [Values] column to column values and line values field with sum aggregation.

 

Please check if it can meet your requirement.

Regards,

Jimmy Tao

[andreako]

Hi Jimmy, 

Really appreciate of your advice, it's works great! 

Sorry abt the original source file, attached again : 

Report

I have further question, if you can help me with it.

How to change the "Period" according from Jan to Dec? 

 

[v-yuta-msft]

Hi andreako,

"How to change the "Period" according from Jan to Dec?"

<--- Could you give more details about your requirement?

Regards,

Jimmy Tao

[andreako]

Dear Jimmy,

Deeply appreciate on the help that given to me.

All my previous problem being solved.

With the new problem, I have further upload my latest report (Report PBIX) with the source file

I wish to Sort the column by Jan 2018 until Dec 2018, so if now i sort the Period, it will follow alphabetical order.

Eg,

Sort by Period 

In the Period.xls i have tried a few maintenance, current upload pbix is based on Format A.

Target to show in the chart is MMM-YY format.

I ever tried the FormatB, the sorting is correct from Jan ~ Dec, but how i change to wording like Jan 2018?

As for Format C, it becomes a date format, but i would to have only MMM-YY without the day,

Key	Period (FormatA)	YYYYMM (FormatB)	MMM-YY(FormatC)
Mth01	Jan'18	201801	Jan-18
Mth02	Feb'18	201802	Feb-18
Mth03	Mar'18	201803	Mar-18
Mth04	Apr'18	201804	Apr-18
Mth05	May'18	201805	May-18
Mth06	Jun'18	201806	Jun-18
Mth07	Jul'18	201807	Jul-18
Mth08	Aug'18	201808	Aug-18
Mth09	Sep'18	201809	Sep-18
Mth10	Oct'18	201810	Oct-18
Mth11	Nov'18	201811	Nov-18
Mth12	Dec'18	201812	Dec-18

Sorry for the long winded explanation, just to share what other options which being tried but still cannot get the ultimate aim.

Thank you in advance.

Andrea Ko