Hi Team,
We have a heirarchy of 7 levels, at level 3 of the heirarchy we will have a total group (ex TOTAL REVENUE) which needs to be calculated based on the other nodes of the same level .
Is there a way we could create a measure to put those calculated values into the particular node. We are using ISINSCOPE function to check the heirarchy is at that level and also one more condition to be put up by checking whether the level3 value is like (TOTAL REVENUE). One challenge we have is the table that we have for heirarchy will have duplicates and we tried to use MAX function to get one value out of it which seems to be not working.
below is a example of DAX we use, any help would be greatly appreciated.
IF(ISINSCOPE(dimaccountstatements[Level3Name]) && (MAX(dimaccountstatements[Level3Name]) = "TOTAL REVENUE (EXCLUDING NETPROFIT INTEREST)"),[TOTALREVENUEEXCLUDINGNETPROFITINTEREST],NetActuals)
Regards,
Sathish
