How to show a table/stacked column chart with moving categories/type and same total.

[nor303]

Hi

I have a table consisting of 3 columns. The saldo and the LTV column are the state of today. LTV1 column is if a shock occurs, if this happens some of the sums in saldo will change letter. I also made an relationship table, but im not sure if its necessary. However in the future I might want to make a LTV2 and so on with different shocks, and thus I thought it might be a good idea to have a table that I can use as a legend or X-axis where the other shocked values can be presented. The problem is that Im not able to make this work. I cant get the different values to show in a stacked column chart or a table like the one in excel. Any idea on how I can do this? Ive tried with many different measures also without luck. Im thinking that I need to get the measure to sum up saldo depending on the letter. I get the correct total, but Im not able to show the distribution across the lettes correctly. Since it either show LTV or LTV1. Thus I tried to make this relationship table, but without getting it to work.

DATA:

Saldo	LTV	LTV1
15	a	b
10	b	c
25	c	d
50	b	b
24	c	d
10	a	a
12	b	c
65	a	b

Relationship table:

LTV

a

b

c

d

How I want it presented in a table

	LTV	LTV1
a	90	10
b	72	130
c	49	22
d		49
Sum	211	211

[Anonymous · Accepted solution]

Hi @nor303 

As you might want to make a LTV2 and so on with different shocks in the future, you can unpivot all LTV columns in advance with Power Query Editor. When new LTV columns added, you don't have to modify the data again.

To unpivot all LTV columns, go to Power Query Editor, select Saldo column, right click on its column header, then select Unpivot Other Columns. 

You will have the table transformed into the following format, with all LTVs in the same column "Attribute" and a/b/c/d listed in another column "Value". Apply the data to Power BI Desktop. 

Then you can use a matrix visual to get the expected result easily. Put "Attribute" column on Columns and "Value" on Rows. 

To create a stacked column chart, you can use "Value" column as a legend or X-axis. 

In this scenario, you can link the Relationship table to the "Value" column in the transformed table.

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[nor303]

Thanks! However the dataset is very large with 300 000 rows and 60 columns, and growing for each quarter. If Im supposed to shock for 3 values it will multiplied by 3 each time. Can it be done easier (less data intensive)?

Edit: Maybe its better to extract the data in a new table and do the work there, at least I dont get all the unessecary columns 3 or more times?

[Anonymous]

Hi @nor303 

Yes, you can extract only the necessary columns into a new table then unpivot columns there. This will keep the data size from growing too much after unpivoting. 

Best Regards,
Jing