Hello i have a sort table with 3 levels and revenue table with details and expense tabel with details which i will use to justget values and measures which is easy, the real challenge for me is to make a matrix table with 3 heirchy.
level 1 contains basic financial report , level 2 is detailed and level 3 is for my minute cost as shown below:
| Level 1 | Level 2 | Level 3 |
| Revenues | source 1 | null |
| Revenues | source 2 | null |
| Revenues | source 3 | null |
| Payroll Expenses | Payroll Expenses | null |
| Payroll Expenses | Accountant | null |
| Payroll Expenses | Employees | null |
| Business Expenses | Business Expenses | null |
| Business Expenses | Other Fees | |
| Business Expenses | Attorney Fees | null |
| Minute Cost | Minute Cost | null |
| Minute Cost | Carrier 1 | Trunk 1 |
| Minute Cost | Carrier 1 | Trunk 2 |
| Minute Cost | Carrier 2 | Trunk 3 |
| Minute Cost | Carrier 2 | Trunk 4 |
| Minute Cost | Carrier 3 | Trunk 5 |
| Minute Cost | Carrier 3 | Trunk 6 |
| Total Expenses | Total Expenses | null |
| Taxes | Taxes | null |
| Net Profit | Net Profit | null |
i want to build a matrix table with the 3 levels:
i know how to build it with 2 levels where i use the formula below:
Financial Value =
IF(
[Total revenue] <> 0,
IF(
ISINSCOPE('Sort table'[Level 2]),
SWITCH(
SELECTEDVALUE('Sort'[Level 2]),
โsource 1โ, [Source 1 sales],
โsource 2โ, [Source 2 sales],
โsource 3โ, [Source 3 sales],
"Accountant", [Accountant expense],
"Employees", [Employee expense],
โOther Feesโ, [Others expense],
โAttorney Feesโ, [Attorney expense],
โCarrier 1โ, [Carrier 1 expense],
โCarrier 2โ, [Carrier 2 expense],
โCarrier 3โ, [Carrier 3 expense]),
SWITCH(
SELECTEDVALUE('Sort table'[Level 1]),
"Minutes Expenses",[ Minute Cost],
"Net Profit",[Net Profit],
"Total Expenses",[ total Expenses]
"Business Expenses",[ Business Expenses],
"Payroll Expenses",[ Payroll Expenses],
"Revenue",[ Total revenue],
"Taxes",[ Total revenue]*tax[taxed])
))
so how can i add a third level heirchy?
