Matrix Problems

[Rantonucci]

Hey PBI community,

I have been using power BI Desktop for a while now. However, Ive been trying to make a budget in a matrix.
kinda like this: 

	jan-17	jan-17	jan-17	feb-17	feb-17	feb-17	so on..	so on..	so on..
	BUDGET	REAL	DESV	BUDGET	REAL	DESV	BUDGET	REAL	DESV
Income	X1	X2	X3	X4	X5	X6	X7	X8	X9
Costs	Y1	Y2	Y3	Y4	Y5	Y6	Y7	Y8	Y9
Margin %	Z1	Z2	Z3	Z4	Z5	Z6	Z7	Z8	Z9

Income and costs come from different tables, which has different columns for Budget and Real.
Hence, I want this matrix to show something like this, however, the categories for income and costs are different so the matrix can not display exactly this format.  keep in mind that income has sub-categories as well as costs. 

I know that I can do this with measures but it would not be so practical, since i would have make 3(budget,real,desv) x 12(months) measures and also Tthey stay fixed in the matrix. 

Thank you for your help!!

Rodrigo

[Rantonucci]

Hey PBI community,

I have been using power BI Desktop for a while now. However, Ive been trying to make a budget in a matrix.
kinda like this: 

	jan-17	jan-17	jan-17	feb-17	feb-17	feb-17	so on..	so on..	so on..
	BUDGET	REAL	DESV	BUDGET	REAL	DESV	BUDGET	REAL	DESV
Income	X1	X2	X3	X4	X5	X6	X7	X8	X9
Costs	Y1	Y2	Y3	Y4	Y5	Y6	Y7	Y8	Y9
Margin %	Z1	Z2	Z3	Z4	Z5	Z6	Z7	Z8	Z9

Income and costs come from different tables, which has different columns for Budget and Real.
Hence, I want this matrix to show something like this, however, the categories for income and costs are different so the matrix can not display exactly this format.  keep in mind that income has sub-categories as well as costs. 

I know that I can do this with measures but it would not be so practical, since i would have make 3(budget,real,desv) x 12(months) measures and also Tthey stay fixed in the matrix. 

Thank you for your help!!

Rodrigo

[v-huizhn-msft]

Hi @Rantonucci,

What do your source table look like? Could you please share your sample data for further analysis? So that we can post the solution which is close to your requirement.

Best Regards,
Angelia

[Rantonucci]

Thank you for your response!

Here's an example:

MONTH	CLIENT	CATEGORY_IN	SUBCATEGORY_IN	BUDGET_IN	REAL_IN
ene-17	GO-BANK	INCOME	APPS-FIN	10.976	13.724
feb-17	CATERPILLAR	INCOME	APPS-CLOTHES	12.740	13.504
mar-17	JOHNSONS	INCOME	APPS-CLOTHES	12.388	14.902
…	WALLMART	INCOME	PRODUCTS	11.350	11.089

Keep in mind that income and costs come from different tables. Costs table look very similar and it has different column names, but the structure is the same.


Rodrigo 

[v-huizhn-msft · Accepted solution]

Hi @Rantonucci,

As I tested, if your income and cost tables have the same structure, you can append them to one, and create a matrix visual using the new one.

1. I create income and cost sample table as follows.

INCOMECOST
2. In query editor, click Append query as new, you will get a new combined table.

3. Create a matirx, select CATEGORY_IN as x-axis, MONTH as COLUMN, the BUGET_IN and REAL_IN as value level, you will get the expected result.

desired result

Best Regards,
Angelia

[Rantonucci]

Thats Clever and it works just fine. 

Thank you!!!