Inserting Calculated Fields in rows in Matrix Visual

[usmansattar]

Hello All,

I am in a bit confusion with Matrix Visual.

I hava a PnL table which have fields (Group, SubGroup, Amount, Company, Month) 

I also made a Dim tabel for Group which have these info

Group	Index
Sales Accounts	1
Cost of Sales :	2
Direct Expenses	3
Gross	4
Indirect Incomes	5
Indirect Expenses	6
Net	7

The "Gross" and "Net" are not part of Fact table those are calculated with DAX.

My Fact table is as below

Group	SubGroup	Amount	Company	Month
Sales Accounts	R-01 Revenues From Waste Treatment	10818.09	ABC	July-23
Sales Accounts	R-03 Revenues from Sales of Addititives and Fuels	108728.87	ABC	July-23
Sales Accounts	R-04 Revenues from Sales of Building Materials	22384	ABC	July-23
Sales Accounts	R-05 Revenues from Rental of Plant & Equipment	22643.33	DEF	July-23
Sales Accounts	Sales Bills to Make	475	DEF	July-23
Cost of Sales :	Opening Stock	837417.98	DEF	July-23
Cost of Sales :	Add: Purchase Accounts	593451.79	DEF	July-23
Cost of Sales :	Less: Closing Stock	174821.07	FIG	July-23
Direct Expenses	D-04 Operating Consumables	69856.03	ABC	July-23
Direct Expenses	D-05 Logistics Costs	10888.5	DEF	July-23
Direct Expenses	D-06 Utilities (Electricity, Water, Gas)	4807.94	FIG	July-23
Direct Expenses	D-07 Direct Labour Cost	165176.88	ABC	July-23
Indirect Incomes	R-09 Revenue of Sales of Assets (book gain)	140000	ABC	July-23
Indirect Incomes	R-12 Recovery or Compensation Payments	-108202	ABC	July-23
Indirect Incomes	R-16 Revenue from Affiliate Companies	-54905.53	DEF	July-23
Indirect Expenses	I-13 Telecommunication Expenses	3066.71	ABC	July-23
Indirect Expenses	I-17 Office Supplies	2337.3	DEF	July-23
Indirect Expenses	I-18 Banking Charges	1068.75	DEF	July-23

Gross is 

Gross Profit = [Total Sales]-[Total Cost Of Sale]- [Total Direct Expense]

and Net is

Net profit = [Gross Profit]+ [Indirect income]-[Total Indirect Expense]

How can i display these two calculated values in rows after Direct Expense and Indirect Expense respectively inside matrix visual..

I made virtual table and union with dim table but then it only shows in a single column, not dividing Company wise and Month wise.

Struggling from a day to achieve this

[Anonymous · Accepted solution]

Hi @usmansattar ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a measure. 

Measure = SWITCH(TRUE(),
SELECTEDVALUE('Fact table'[Group])="Direct Expenses",[Gross Profit],
SELECTEDVALUE('Fact table'[Group])="Indirect Expenses",[Net profit],
BLANK())

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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