Urgent! Hierarchy problem in Cashflow!

[KXNIKKI]

Hello everyone,

I could use some assistance. I've tried numerous times but still can't achieve the expected results. Any help would be greatly appreciated.

Here is the cash flow setup that I've established(best try....):

 

-------------------------------------------------------------------------------------------------------------------
Below is the DAX formula that I've written:

C/F Values Forecast =
VAR CurrentItem = SELECTEDVALUE('CF Template'[Cash Flow Normalized])

RETURN
SWITCH(
TRUE(),
CurrentItem = "OTHER INCOME", [Sum of FORECAST OTHER INCOME],
CurrentItem = "FINANCING ACTIVITIES INFLOW", [Sum of FINANCING ACTIVITIES INFLOW],
CurrentItem = "EQUIPMENT & CAPEX", [Sum of FORECAST EQUIPMENT & CAPEX],
CurrentItem = "SALARY+ADMINISTRATION FEES", [Sum of FORECAST SALARY+ADMINISTRATION FEES],
CurrentItem = "UTILITIES+ OTHER", [Sum of FORECAST UTILITIES+ OTHER],
CurrentItem = "FINANCING ACTIVITIES OUTFLOW", [Sum of FORECAST FINANCING ACTIVITIES OUTFLOW],
CurrentItem = "TRADE/ NON DEBTOR/ INTERCO", [Actual AR payment invoice value],
CurrentItem = "INTERCO", CALCULATE([Actual AR payment invoice value], FILTER(Invoice, Invoice[CLASS] = "INTERCO")),
CurrentItem = "TRADE RECEIVABLES", CALCULATE([Actual AR payment invoice value], FILTER(Invoice, Invoice[CLASS] = "TRADE RECEIVABLES")),
CurrentItem = "NON TRADE RECEIVABLES", CALCULATE([Actual AR payment invoice value], FILTER(Invoice, Invoice[CLASS] = "NON TRADE RECEIVABLES")),
CALCULATE( [FORECAST Cash Flow Values], FILTER( 'Cash Flow Forecast Data', 'Cash Flow Forecast Data'[Cash Flow Sub Category] = CurrentItem ) ))

-------------------------------------------------------------------------------------------------------------------

This is the cash flow template I'm utilizing:

-------------------------------------------------------------------------------------------------------------------
My expectation is as follows:
The row labels should remain in place to ensure the sequence of the cash flow items is maintained.
The report should include two levels: Level 1 (cash flow summary items), which when collapsed will reveal Level 2.(example as below)

please advice if any setting i need to take note , thank a lots

Thank you in advance for your guidance.

[Anonymous · Accepted solution]

HI @KXNIKKI,

I think these table structures should not be suitable to achieve the hierarchy levels.

You may need to modify these fields value to convert them to general fields that can be used in matrix visual to show the expected results.

Perhaps you can take a look at the following offical video about hierarchy structure in power bi if helps:

How to create and use a Power BI Hierarchy - YouTube
For use Dax expression to check current hierarchy level, you can refer to the following blog:

Clever Hierarchy Handling in DAX - SQLBI

Regards,

Xiaoxin Sheng