cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rantonucci Frequent Visitor
Frequent Visitor

Matrix Problems

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-17jan-17jan-17feb-17feb-17feb-17so on..so on..so on..
 BUDGETREALDESVBUDGETREALDESVBUDGETREALDESV
IncomeX1X2X3X4X5X6X7X8X9
CostsY1Y2Y3Y4Y5Y6Y7Y8Y9
Margin %Z1Z2Z3Z4Z5Z6Z7Z8Z9


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

1 ACCEPTED SOLUTION

Accepted Solutions
v-huizhn-msft Super Contributor
Super Contributor

Re: Matrix Problems

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.

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

4.PNG3.PNG

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.

5.PNGdesired result

Best Regards,
Angelia

5 REPLIES 5
Rantonucci Frequent Visitor
Frequent Visitor

Matrix Problems

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-17jan-17jan-17feb-17feb-17feb-17so on..so on..so on..
 BUDGETREALDESVBUDGETREALDESVBUDGETREALDESV
IncomeX1X2X3X4X5X6X7X8X9
CostsY1Y2Y3Y4Y5Y6Y7Y8Y9
Margin %Z1Z2Z3Z4Z5Z6Z7Z8Z9


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 Super Contributor
Super Contributor

Re: Matrix Problems

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 Frequent Visitor
Frequent Visitor

Re: Matrix Problems

Thank you for your response!

Here's an example:

MONTH CLIENTCATEGORY_INSUBCATEGORY_INBUDGET_INREAL_IN
ene-17GO-BANKINCOMEAPPS-FIN          10.976   13.724
feb-17CATERPILLARINCOMEAPPS-CLOTHES          12.740   13.504
mar-17JOHNSONSINCOMEAPPS-CLOTHES          12.388   14.902
WALLMARTINCOMEPRODUCTS          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 Super Contributor
Super Contributor

Re: Matrix Problems

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.

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

4.PNG3.PNG

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.

5.PNGdesired result

Best Regards,
Angelia

Rantonucci Frequent Visitor
Frequent Visitor

Re: Matrix Problems

Thats Clever and it works just fine. 

Thank you!!!