Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

One Measure To get 3-level hierarchies In Matrix Table

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 1Level 2Level 3
Revenues                             source 1                              null
Revenuessource 2null
Revenuessource 3null
Payroll ExpensesPayroll Expensesnull
Payroll ExpensesAccountantnull
Payroll ExpensesEmployeesnull
Business Expenses Business Expensesnull
Business Expenses       Other Fees 
Business ExpensesAttorney Feesnull
Minute CostMinute Costnull
Minute CostCarrier 1Trunk 1
Minute CostCarrier 1Trunk 2
Minute CostCarrier 2Trunk 3
Minute CostCarrier 2Trunk 4
Minute CostCarrier 3Trunk 5
Minute CostCarrier 3Trunk 6
Total ExpensesTotal Expensesnull
TaxesTaxesnull
Net ProfitNet Profitnull

 

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?

3 REPLIES 3
v-cazheng-msft
Community Support
Community Support

Hi @Anonymous,

 

May I know whether your problem has been resolved? If you still have problems on it, could you please make it more clear?

 

How to Get Your Question Answered Quickly - Microsoft Power BI Community

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!

 

Best Regards,

Community Support Team _ Caiyun

Anonymous
Not applicable

@Jihwan_Kim @SpartaBI @amitchandak @daXtreme  @tamerj1 id appreciate your help

Anonymous
Not applicable

anyone?

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.