Issue on Power Pivot/Query Automatically Merge Duplicate Data

[tksnota]

Hi,

Is there a way to address the above issue because the sum of the pivot table and the original sheet is always different?

//NT

 

[tksnota]

i want to show the different values of the amount_dc per job no e.g. 2023-200 so this is the reason it multiplies values by 47x

How can I do it the above with the 47x duplication? do I need to use the value in pivot to this expression?

 

[RoryA]

You have to put something in the values field area, even if it's just a count of some field from the many side of the relationship, otherwise the relationship between the two tables will not work, and you will see all values replicated for every row.

 

[tksnota]

Being new in using power query/pivot so I just want to know that putting values eg count sum on the power pivot tab will eliminate the 47x duplicates. Is that right?

 

[RoryA]

Try it and see?

 

[tksnota]

Hi

I add a measure inside the value table as shown from the power pivot's screenshot with Total as the Field Name using the values of Amount_DC and got this result.

It doesn't eliminate the duplicates. Anything I'm doing wrong???

 

[tksnota]

this is how it looks when the proposals are not collapsed

 

[RoryA]

What is Total?

 

[tksnota]

I managed to get some headway by adding values on the power pivot's values field as shown. The Value (WIP) is correct at £709k+ but the amount in DC should be -199k+ however it only produced +36k+

I uploaded a new sets of Power Query/Pivot and Source Data in Excel so that someone can tell me what I'm doing wrong so I can do the appropriate corrections

Thanks a lot...

 

[tksnota]

@RoryA - the total should be the Amount_DC which I'm trying to get but my new power pivot and query is only showing £36k+. I'd uploaded new sheets on the drive so you can see and recommend what I need to do in order to get the -£199k

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: SUM function not capturing All Amount
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.