Table consolidating incorrectly

Power BI
1

Hi, I have created a table, with the following formula:

CY MTD. =
VAR CURRENTITEM = SELECTEDVALUE( ‘Detailed P&L’[Items (Normalized)])

RETURN
CALCULATE( [IS.] , FILTER(Detail , Detail[Description]= CurrentItem))

However, it appears to be consolidating the same line descriptions together and putting them under each department:

image
image927×185 13.6 KB

What changes do I need to make to the DAX to separate these?

Thanks.

Stuart

2

Hello @Stuart,

Thank You for posting your query onto the Forum.

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. :slightly_smiling_face:

Important Note: Please click onto the link to view the entire post and not onto the expand/collapse button.

Thanks and Warm Regards,
Harsh

3

Thanks, I was hoping to find a solution whereby I did not need to rename each line (had 1,000+ lines), but did it.

4

Hello @Stuart,

You’re Welcome. :slightly_smiling_face:

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.

Thanks and Warm Regards,
Harsh

1 Like
5

Hi Harsh

After doing this, it split it correctly, but for some reason my budget column figures do not pull through:

image
image967×121 8.7 KB

I have made sure that the descriptions where changed in each file correctly. Any idea how to troubleshoot this?

6

Hello @Stuart,

It would be great if you can upload the working of the PBIX file for the reference. So that members of our forum can assist you in a better and efficient manner.

Thanks and Warm Regards,
Harsh

7

Hi Harsh

Due to the confidential nature of the file, I cannot upload it - it does however, appear to be due to the “-”. Is this possible?

8

Hello @Stuart,

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.

Thanks and Warm Regards,
Harsh

9

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.
Greg