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

[HarryR1]

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
1	1004 - Parent Project

<tbody>
[TD="width: 76"] £500 [/TD]

[TD="width: 52"] 2 [/TD]
[TD="width: 210"] 1004 - Sub Project [/TD]
[TD="width: 76"] £200 [/TD]

[TD="width: 52"] 3 [/TD]
[TD="width: 210"] 1004 - Sub Project with same name as Parent Project [/TD]
[TD="width: 76"] £300 [/TD]

</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]

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]

I would expect sometihng similiar to this:

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

 

[HarryR1]

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]

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.

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]

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]

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

 

[HarryR1]

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]

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]

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 ?

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
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Project Name (Relationship Link)[/TD]
[TD]Cost[/TD]
[TD]IsTheMax[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]100[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]50[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]Project B[/TD]
[TD]100[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]Project C[/TD]
[TD]100[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]SUM Total[/TD]
[TD]350[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

FINAL TABLE
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Project Name (Relationship link)[/TD]
[TD]Cost[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]150[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project B[/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project C[/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SUM Total[/TD]
[TD]350[/TD]
[TD]Total should only be 300[/TD]
[/TR]
</tbody>[/TABLE]

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]

=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