100% stacked column with line

[dannytan1112]

Hi,

I have already other cases, still got a problem here.

How to make a stacked 100% with line where 100% based on the sales per day(legend:Sales A,B, and C) and line PR_ratio per day with the value on the right y-axis?

Thanks a lot

Let's say I have 5 columns

Date	SalesA	SalesB	Sales C	PR_ratio
15/12/2022	1000	800	600	0.8
16/12/2022	800	550	230	0.4
17/12/2022	300	500	700	1.3
18/12/2022	600	300	400	1.6
19/12/2022	500	200	500	1.1

[TomasAndersson · Accepted solution]

Ok, so you have two main issues:
1. To use a legend, you would want the sales data to be in the same column, with a new, separate column describing what attribute (A, B, C) the sales value refer to.
2. Power BI do not have a stacked 100% column chart with a line (for some reason), only a "regular" stacked bar chart. You need to create a measure to compensate for that.

1.
Can be solved in different ways. Depending on your source data (e.g. Excel) you can fix it there. But you can also use Power Query. If you go to your model, "Edit query" on the table and then under "Transform" choose "Unpivot columns" while having your three columns selected you get a result as below - with one column for all sales values and one column for all attributes. You might want to modify column names before doing the transform. 

This form makes it easier to work with.

2.
First, create a measure that modifies the sales value to the percentage of total sales for each day:

Sales % of total = 
DIVIDE(
    sum('Table'[Value]),            //Sum of sales (in current context, i.e. per date and attribute in this case)
    CALCULATE(sum('Table'[Value]),
    ALLSELECTED('Table'[Attribute])  //Total sales - sum of sales on this date for all attributes 
    )
)

Then you can use the Line and stacked column chart visual to create your 100% column chart with a line: 

Hope this helps!

[dannytan1112]

Thanks a lot Tomas,

Let me try it. Really appreciate it. Thanks