# PowerPivot model: Duplicate rows adding up to over inflated total.



## HarryR1 (Dec 8, 2014)

Hi all
I am new to PowerPivot and data models, and I am querying an SSAS cube in a PowerPivot data model. I have a problem with duplicate names inflating figures. The duplicates come from a Project Server SSAS cube. 

*Row**Project Name**Cost*11004 - Parent Project£50021004 - Sub Project£20031004 - Sub Project with same name as Parent Project£300

<tbody>

</tbody>Rows 2 and 3 are both sub projects that make up the whole project that is totalled up in row 1. However, when looking a the results in PowerPivot, the model is adding all these three rows separately, producing a total figure of £1000.
How can I get the model to a)recognise the hierarchy from Project Server. it does if querying the cube directly in excel into a pivot chart but not in a PowerPivot model. 
Is there a DAX or MDX calculation I can use to only select or count the highest figure of any duplicates rows with the same name?
I understand it's a pretty complex question but hoping someone can help.
Thanks in advance.
Harry


----------



## scottsen (Dec 8, 2014)

So, in the your sample, there is just NOTHING that makes it clear there is a parent/child relationship between those rows.  You have two choices... (at least )
1) What you are referencing... create a calculated column which only has a value if it's the highest value of its grouping.
2) actually bring in some parent/child info.  (You seem to have a Row# column is there a ParentRow you can bring in?)

the first should generally be easier, but ... does have some risks.   Negative #'s could really mess w/ you and ... a parent w/ just 1 child... both would have the same value.  Need to be careful not to include both in that scenario.   (So, logic that is like... "only include this node if its the largest in the set..." would typically include _both_ which isn't what you want).

We can probably sleeze our way around the 2nd issue.... the negative issues seems harder.
Parent 500
  Kid1   600
  Kid2  -100

We would choose Kid1, since it is largest.

Is that an issue for you?


----------



## HarryR1 (Dec 8, 2014)

scottsen said:


> So, in the your sample, there is just NOTHING that makes it clear there is a parent/child relationship between those rows.  You have two choices... (at least )
> 1) What you are referencing... create a calculated column which only has a value if it's the highest value of its grouping.
> 2) actually bring in some parent/child info.  (You seem to have a Row# column is there a ParentRow you can bring in?)
> 
> ...



scottsen

Thanks for your reply.

The row numbers are just for illustrative purposes, there actually isn't anything else apart from the project title that identifies a project (lazy cube work in my opinion).

Creating a calculated column that only picks the highest of the rows with the same name sounds ideal, except I don't know yet how to write a formula that compares the names of a rows, identifies duplicates and only shows the value of the highest one in the set. 

Could you point me in the direction of a function/formula or resource area where I can research how to do it?

Thanks


----------



## scottsen (Dec 8, 2014)

Yep, I can help there 

So, no issues w/ negative numbers or "just 1 sub-project" to worry about?

Something like:

```
=MyTable[Cost] = 
     CALCULATE(MAX(MyTable[Cost]), ALL(MyTable), MyTable[Project Name] = EARLIER(MyTable[Project Name]))
```


----------



## Kazlik (Dec 10, 2014)

I had a similar issue last week with an event log and duplicate values for coverage_id's. I made the two Measures below.

MaxTime:=MAX(Events[TimingMinutes])
DistinctAvgTimeMinutes:=AVERAGEX(DISTINCT(Events[coverage_id]),[MaxTime])


----------



## HarryR1 (Dec 15, 2014)

scottsen said:


> Yep, I can help there
> 
> So, no issues w/ negative numbers or "just 1 sub-project" to worry about?
> 
> ...



Thanks a lot

I am going to give those a whirl and will update the thread with my findings. Thanks again.


----------



## HarryR1 (Dec 15, 2014)

scottsen said:


> Yep, I can help there
> 
> So, no issues w/ negative numbers or "just 1 sub-project" to worry about?
> 
> ...



Hi Scottsen

That worked great in that it marked a 'true' next to unique names and a  'false' next to repating project names with a lower value.

Now I just need to call these into another table but only the ones that are marked as true. Could you advise me how to do that? 

BTW: This has inspired me to dive into learning as much DAX as I can . 


Thanks

Harry


----------



## HarryR1 (Dec 15, 2014)

Thanks Kazlik.


----------



## HarryR1 (Dec 15, 2014)

To be more specific:

I am using this solution in a table that acts as a lookup for a column in another table. 

How can I amend the lookup in my target table's calculated column to only fetch the value from the project name marked with a 'True' value?

Lookup Table.
Project A: 100
Project A: 50

Target Table.
Project A: 100

Thanks again.


----------



## scottsen (Dec 15, 2014)

I get pretty nervous when you start copying data around between tables.  Sooome reference to that in my most recent blog post... it's a vlookup/old-skool-excel thing to do.

The theory is that you just need to apply a filter on that new column... to only include the ones that are marked true.  The application of said theoy will depend on what you are trying to do 

LOOKUPVALUE will probaby work for you.  It can take multiple columns as input... one of which would be the "IsTheMax" column with a value of True.


----------



## HarryR1 (Dec 8, 2014)

Hi all
I am new to PowerPivot and data models, and I am querying an SSAS cube in a PowerPivot data model. I have a problem with duplicate names inflating figures. The duplicates come from a Project Server SSAS cube. 

*Row**Project Name**Cost*11004 - Parent Project£50021004 - Sub Project£20031004 - Sub Project with same name as Parent Project£300

<tbody>

</tbody>Rows 2 and 3 are both sub projects that make up the whole project that is totalled up in row 1. However, when looking a the results in PowerPivot, the model is adding all these three rows separately, producing a total figure of £1000.
How can I get the model to a)recognise the hierarchy from Project Server. it does if querying the cube directly in excel into a pivot chart but not in a PowerPivot model. 
Is there a DAX or MDX calculation I can use to only select or count the highest figure of any duplicates rows with the same name?
I understand it's a pretty complex question but hoping someone can help.
Thanks in advance.
Harry


----------



## HarryR1 (Dec 16, 2014)

scottsen said:


> I get pretty nervous when you start copying data around between tables.  Sooome reference to that in my most recent blog post... it's a vlookup/old-skool-excel thing to do.
> 
> The theory is that you just need to apply a filter on that new column... to only include the ones that are marked true.  The application of said theoy will depend on what you are trying to do
> 
> LOOKUPVALUE will probaby work for you.  It can take multiple columns as input... one of which would be the "IsTheMax" column with a value of True.



Hi Scottsen

Thanks for the tip. I tried LOOKUPVALUE but can't get it to work. I want to tell LOOKUPVALUE to lookup the cost against the project name and only return the cost row that has a 'true' value in the 'IsTheMax' column. It sounds simple but how do I tell the LOOKUPVALUE to look for a 'true' statement in 'ISTHEMAX' column?

Thanks

Harry


----------



## scottsen (Dec 17, 2014)

I would expect sometihng similiar to this:

=LOOKUPVALUE(TheTable, TheTable[ProjectName], TargetTable[ProjectName], TheTable[IsTheMax], TRUE())


----------



## HarryR1 (Dec 18, 2014)

scottsen said:


> I would expect sometihng similiar to this:
> 
> =LOOKUPVALUE(TheTable, TheTable[ProjectName], TargetTable[ProjectName], TheTable[IsTheMax], TRUE())




Hi 

Thanks for the assist but it's not working. The syntax looks like this:

=LOOKUPVALUE(costtable, costtable[cost], targettable[newcolumn], costtable[IsTheMax], TRUE())

There is a relationship between the project name column in each table. Also, in the formula, there is no reference to the cost column that values that I want.

I have tried different variations but no luck.


----------



## HarryR1 (Dec 18, 2014)

HarryR1 said:


> Hi
> 
> Thanks for the assist but it's not working. The syntax looks like this:
> 
> ...



I think what may be going on here is that LOOKUPVALU require me to specify a value like an individual project name in the formula, but I want to call the entire cost column IF the IsTheMax=True.

I don't think LOOKUPVALUE is going to work if it requires a specific value from a row(project name?)


----------



## HarryR1 (Dec 18, 2014)

Is there a way to make a filtered view view (IsTheMax=True) carry forward to the target table? Currently, if I filter the IsTheMax column to True the target table still shows all of the values for items marked False.


----------



## scottsen (Dec 18, 2014)

Do you want to share your workbook (dropbox,onedrive,googledrive) and i can take a quick look at what you got going on?


----------



## HarryR1 (Dec 18, 2014)

scottsen said:


> Do you want to share your workbook (dropbox,onedrive,googledrive) and i can take a quick look at what you got going on?



I would if I could but it contains confidential financial data from work and the data is from a cube.

Thanks for the offer though.


----------



## scottsen (Dec 18, 2014)

So, you original post said dupes were a problem, and we added this IsMax column.   I guess my expectation would be that you have some measure:
  Total Value := SUM(MyTable[Value])

That should now be modified to 
  Total Value := SUM(MyTable[Value], MyTable[IsMax] = TRUE())

Does that make sense, or ?


----------



## HarryR1 (Dec 19, 2014)

scottsen said:


> So, you original post said dupes were a problem, and we added this IsMax column.   I guess my expectation would be that you have some measure:
> Total Value := SUM(MyTable[Value])
> 
> That should now be modified to
> ...



Hi 

I am not completely sure what the above means sorry . Are you suggesting that I the calculation in my Final table should be =SUM(CostsTable[sum of cost], CostsTable[IsThemax] = TRUE())

I have a cost table that has duplicate project names in it. The IsTheMax column was applied as per your help to that same table. So the IsTheMax column contains True or False.

I need to get the costs for the projects into a Final table but only if the IsTheMax in the cost table is TRUE. This is because the duplicate project names in the cost table are being added together for a single project name in the Final table. Both tables are linked by relationship using the project name, however I have made the Final table only show a single project name and remove duplicates. I was able to do this because the source of the data is different from the Costs table and more refined and I was able to filter by other useful measures. This is not available in the source for the costs table.  

So far, the Cost table only has one Sum aggregation; for the costs column. The cost is the only measure in the costs table.

Are you suggesting that I change the calculation in the SUM cell of costs column to the formula you have suggested above?

*COSTS TABLE*

*Project Name (Relationship Link)**Cost**IsTheMax*Project A100TRUEProject A50FALSEProject B100TRUEProject C100TRUE*SUM Total**350*

<tbody>

</tbody>
*FINAL TABLE*

*Project Name (Relationship link)**Cost*Project A150Project B100Project C100*SUM Total**350**Total should only be 300*

<tbody>

</tbody>
So I need a column in the Final table that looks up the [cost] column from the Costs table and only uses the row marked True instead of both from the duplicate project names.

LOOKUPVALUE didn't work. I think because it wants a project name specifying for the look-up, whereas I want the entire column and all rows to be referenced against the project name n my Final table. 

Sorry to be a pain and I really do appreciate your help so far.

Harry


----------



## scottsen (Dec 19, 2014)

> =SUM(CostsTable[sum of cost], CostsTable[IsThemax] = TRUE())


More or less, though I would actually expect:
=SUM(CostsTable[*cost*], CostsTable[IsThemax] = TRUE())

Right... your total cost is the sum of all costs... where Max is true.  That is what that measure says.

I should have a window from 1pm to 4pm PST today... and in the spirit of the holidays wouldn't mind jumping on a shared screen consult for a half hour to get you jump started.  Reach out via email to scott [at] tinylizard.com if you want to do that and see this in time


----------



## HarryR1 (Dec 8, 2014)

Hi all
I am new to PowerPivot and data models, and I am querying an SSAS cube in a PowerPivot data model. I have a problem with duplicate names inflating figures. The duplicates come from a Project Server SSAS cube. 

*Row**Project Name**Cost*11004 - Parent Project£50021004 - Sub Project£20031004 - Sub Project with same name as Parent Project£300

<tbody>

</tbody>Rows 2 and 3 are both sub projects that make up the whole project that is totalled up in row 1. However, when looking a the results in PowerPivot, the model is adding all these three rows separately, producing a total figure of £1000.
How can I get the model to a)recognise the hierarchy from Project Server. it does if querying the cube directly in excel into a pivot chart but not in a PowerPivot model. 
Is there a DAX or MDX calculation I can use to only select or count the highest figure of any duplicates rows with the same name?
I understand it's a pretty complex question but hoping someone can help.
Thanks in advance.
Harry


----------



## HarryR1 (Dec 23, 2014)

scottsen said:


> More or less, though I would actually expect:
> =SUM(CostsTable[*cost*], CostsTable[IsThemax] = TRUE())
> 
> Right... your total cost is the sum of all costs... where Max is true.  That is what that measure says.
> ...



Hi Scottsen

Apologies for not replying sooner. I have had a winter cold the last few days which took me out of action. I will give the above a try and see if I can make some progress.

Thanks for all your help so far, it's super appreciated.

Harry


----------



## HarryR1 (Dec 24, 2014)

scottsen said:


> More or less, though I would actually expect:
> =SUM(CostsTable[*cost*], CostsTable[IsThemax] = TRUE())
> 
> Right... your total cost is the sum of all costs... where Max is true.  That is what that measure says.
> ...



"Too many arguments were passed to the SUM function. The maximum argument count for the function is 1".

I get this when trying to add to a new calculated column in both tables and as a calculated field.

Thanks for all your help but I am going to give up now. It's far too difficult to use what should be a very simple calculation.

Thanks again.


----------



## scottsen (Dec 24, 2014)

Now you are gonna make me feel bad n stuff!    Cuz I was apparently drunk when I wrote the equation.
=SUM(CostsTable[cost], CostsTable[IsThemax] = TRUE())
is totally not valid.

=CALCULATE(SUM(CostsTable[Cost]), CostsTable[IsTheMax] = TRUE())

Is what you wanted.


----------



## HarryR1 (Dec 27, 2014)

scottsen said:


> Now you are gonna make me feel bad n stuff!    Cuz I was apparently drunk when I wrote the equation.
> =SUM(CostsTable[cost], CostsTable[IsThemax] = TRUE())
> is totally not valid.
> 
> ...




Haha. Ok I will give it a go when |I get back home from the holidays. Thanks again


----------



## Kurt (Dec 28, 2014)

HarryR1 said:


> Haha. Ok I will give it a go when |I get back home from the holidays. Thanks again



Maybe you guys can use TeamViewer - Free Remote Control, Remote Access & Online Meetings and do a live session.

I do this all the time with a few really good friends on here who help me.

Just a thought!

Hope this helps.


----------



## HarryR1 (Jan 14, 2015)

Kurt said:


> Maybe you guys can use TeamViewer - Free Remote Control, Remote Access & Online Meetings and do a live session.
> 
> I do this all the time with a few really good friends on here who help me.
> 
> ...



Hi

I was sure I replied to this after Christmas, but upon needing to check it again, I spotted that the post didn't...post.

Everything worked well. Thank you so much for your help. I am looking at a couple of books to learn DAX properly, particularly DAX Formulas for PowerPivot by Rob Collie. DO you know if it's a good resource? Could you recommend any that you have found useful?

Thanks again and Happy New Year!

Harry


----------



## theBardd (Jan 14, 2015)

HarryR1,

Haven't read it yet myself, but DAX Patterns by ALberto Ferrarri and Macus Russo is bound to be good (probably after you have read Rob's book). I also like Kasper de Jonge's book Dashboarding and Reporting with PowerPivot and Excel.


----------



## scottsen (Jan 15, 2015)

Rob's books are *great*.  very approachable and practical, I would definately start w/ the one you picked.


----------

