Well by looking at the scenario right now, the problem is not with the formula but it’s with the naming convention that being defined into the format. Since the same naming convention has been followed for all the “Detailed P&L” particulars it’s causing the problems and providing you the errorneous/ambiguous results.
I’m providing you a link of the post where this same sort of problem was highlighted by one our forum member and how it got resolved.
The only solution to resolve this type of problem is to create a unique naming convention. Please go through the link provided below which will guide you how to solve this problem.
Hoping you find this useful and solves your problem.
Important Note:Please click onto the link to view the entire post and not onto the expand/collapse button.
My apologies but this is the only way around which is available as of now because when we have the same naming conventions it acts like a LOOKUP identifier wherein it’ll just recognizes only the unique names. And if we’ve duplicate names then it’ll just simply consider the first record of the data while evaluating the results and will provide the same results for the duplicate records as well.
So in a way you can say, it’s like VLOOKUP, HLOOKUP or XLOOKUP but there’s no wayout available like INDEX-MATCH wherein it considers and provides the results inspite of duplicate records available into our dataset.
And lastly, I’m glad that you found this solution helpful and was able to meet your requirements.
Well this could prove difficult for us to judge without looking at the file. There could be several reasons why it’s not providing the results. It could be due to formula referencing error, typo error while writing the formula, difference between the naming conventions, etc. or you can also check whether those line items have the budget allocation or not.
But as I said without looking at the file it’ll just be the guessing work for the forum members.
Hi @Stuart. A work-in-progress PBIX really helps the forum members. For this and future posts, consider creating sample data that illustrates your issue, or you can sanitize your data using @BrianJ’s technique.