How to combine 6 charts into one

[o59393]

Hi all

I'd like to know if it's possible to merge 6 graphs into one since they follow the same structure. Which basically is a date (from January to December) and then the capacity and the demand.

The dates go from January 2023 to December 2028, 6 years totals.

Due to the way I built the measures and columns I can only have it like this:

But instead I would like this to be the solution, just one graph:

Pleas take a look at the Power BI and the excel sample if needed too.

sample_data_23-28.pbix

SKU by line - Official.xlsx

Note: please click on the image to zoom in.

Thanks.

[Ritaf1983 · Accepted solution]

Hi @o59393 
Generally, you can use small multiples to achieve this goal :
https://learn.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-small-multiples
with 1 row and 6 columns 
year is a "small multiple dividers"
According to your data you need to create a model with standard "star schema" to make it possible
https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

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

[Ritaf1983 · Accepted solution]

Hi @o59393 
Generally, you can use small multiples to achieve this goal :
https://learn.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-small-multiples
with 1 row and 6 columns 
year is a "small multiple dividers"
According to your data you need to create a model with standard "star schema" to make it possible
https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

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

[o59393]

Hi @Ritaf1983 

The thing is that the date and capacity have 6 columns (one per year) and the demand has 6 measures.

I need to have them all in one graph but not by small multiples. 

Thanks.

[Ritaf1983 · Accepted solution]

Hi @o59393 
We can't merge graphs to 1 just according to their structure.

So this is small multiples or...multiple graphs.

You can merge the objects by grouping them like on PowerPoint, but it is not the same.
There is an idea of a similar issue you can vote for it and add your comments:
https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=fd6d22a8-9a7c-40d5-9ac5-4cd4c28e833d

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

[o59393]

Hi @Ritaf1983 

Understood. So I was thinking another possible way, thru Power Query.

See below, I created 6 date columns:

And also 6 capacity columns:

What I'd like to know if it's possible is to unpivot these columns where:

Date Year 1 will pair Capacity - Real Year 1

Date Year 2 will pair Capacity - Real Year 2

Date Year 3 will pair Capacity - Real Year 3

...

...

Date Year 6 will pair Capacity - Real Year 6

I had unpivoted the 6 column dates into one and looks well: 

However i dont know how to do the capacity pair part I described.

I attach you the pbix with this Power Query addition in case you have an idea.

This is how it looks like so far (closer to what I need):

sample_data_23-28_v2.pbix

Thanks!

[Ashish_Mathur]

Hi,

I'd like to try but with only one of those tables and with lesser data (rows).  Could you reshare the PBI and Excel file and in another tab of the Excel file, please show the expected result in a Table format.

[o59393]

Hi @Ashish_Mathur , hope you're doing well!

I did some power query steps to shape the data to find a potential solution, not very elegant though. Hopefully you can find/share an easier way to solve it.

The expected result is in the excel tab called 'Desired Result', also it has less rows as requested.

Hope this works:

sample_data_23-28_v3.pbix

SKU by line - Official.xlsx

Thanks a mill!

[Ashish_Mathur]

Hi,

I do not understand your desired result worksheet at all.  Someone else who does will help you.  Take a much smaller sample dataset (fewer rows/columns) and on that easy to undertand/smaller dataset, show the expected result.

[o59393]

Hi @Ashish_Mathur 

I did a drastic reducion on the excel file (just 36 rows).

So to explain myself, the goal is to shape the original excel data (SKU by line sheet) in order to convert the date and capacity columns (which are 6 each) into one each, in order to have only one trend graph from 2023 to 2028 and not 6 graphs (as shown in the initial question).

The date columns were added in the Power Query (went up from 2023 until 2028) with the formula 'Date.AddYears', and the capacity columns were loaded from the original excel.

So the solution I came up with was basically an unpivot of date and capacity columns, and then did a match of the attribute columns (see Power Query Step called 'Added Custom5'). The logic was If Year 1 = Year 1, then TRUE and False If Year 1 = Year 2 for example. So I kept the TRUE ones.

Finally I took the demand column and applied an IF formula on the column 'Demand All Years' to increase the demand throughout the years 2023 to 2028.

Here is the result (correct one):

I attach you the pbix and xls files. If you think there is an easier way to what I described above, I'd like to know, but if that's not the case or still feel confused that's ok.

sample_data_23-28_v4.pbix

SKU by line - Official v2.xlsx

Note: the expected result in the excel tab, was a copy and paste from the power bi table ''SKU by line - Official'', removing a few columns.

 Thanks.

[o59393]

Hi @Ashish_Mathur ok let me reduce it even more and will share with you.

Thanks and apologies for the confusion.