Monthly Sales Vs capacity - column and line chart

[TiagoLopes]

Hello all,

First time posting.

I am currently using Power Bi for different visualizations but I am struglling with one in particular (and one that I really need).

Data source: excel file

Data format:

Table1:

Table 2:

I want to do 2 things:

1st - Display for Article 1/Article 1.1/Article 1.2 Monthly sales by month. Note - Article 1 is unique, article 1.1/1.2 can be be repeated and diferent combinations of article 1.1/1.2 will originate an unique Article 1. 

My target visualization is a column and line chart where:

Column = Total Sum of the same article 1.1/article 1.2 (table 1)

Line = Sales capacity from (table 2)

I have a slicer where I can pick article1.1/1.2 number and get that graph.

This is what I am trying to do:

Current result in Power BI:

I am facing several problems:

- columns with no space between them

-not possible to include line chart and have month/month analysis with current data format.

-correct relation between table 1 and table 2 (?)

I have tried some diferent ways to power query the data but it seems to complicated and I am not achieving the correct result anyway.

*this data is an example - I have much more columns/rows. My visualization works perfectly - I can do all the analysis I want except for this one.

Can you advise me on how to get the result I want?

Thank you in advace.

Regards

[TiagoLopes]

Dear,

I am trying to fix it. I will let you know if it’s final solution.

Thank you

Tiago

[v-juanli-msft · Accepted solution]

Hi @TiagoLopes 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[v-juanli-msft]

Hi @TiagoLopes 

Since there are repeated "3235" in Aricle1.2 in Table2, it is hard to define the relationship between Table1 and Table2.

So i create index columns in both table for creating such visuals.

Please open my pbix to see more details.

It is apprecaited to clear me the relationship betwwen two tables.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TiagoLopes]

Hello @v-juanli-msft

Thank you for your support.

Your solution seems possible but I am struggling with the Index column creation. In your example you are linking the index column with a1.2.

In my case I can only create a sequencial index.. 1,2,3,4,5 with no link with a1.2.

If I can create the correct index I believe I would be able to do it.

Thank you again

Regards

[v-juanli-msft]

Hi @TiagoLopes 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[v-juanli-msft]

Hi @TiagoLopes 

a1.1 is the column name "Article 1.1" in your Table1.

In Queries Editor, create an index column,

Then select "country","a1","a1.1","a1.2","Index" columns, Transform->unpivot other columns

rename "Attribute","Value" as "date1","Value1"

Then close&&apply, you will see my dataset.

I did the same thing for Table2.

Then in Table1, i create a column

month&index1 = FORMAT(Table1[date1],"yyyymm")&[Index]

in Table2, i create a column

month&index2 = FORMAT(Table2[date2],"yyyymm")&[Index]

create relationship based on these two columns for Table 1 and Table 2

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TiagoLopes]

So I could basically do Power Query -> Transform -> Unpivot

Now I am facing 2 problems:

- All my other data is getting impacted by the Query (let's say initial total volume = 3M / total volume after query =12M)

- I still cannot manage to have do the 2 graphics (column + line ) in one graph. But I can do them individually.

In case I try to merge them the following happens (note that I want to use just 1 Y axis and not 2):

Any support is more than welcome 🙂

Thank you