Forum Discussion
Inserting Calculated Fields in rows in Matrix Visual
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
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.
1 Reply
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.