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]

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

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]

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

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

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]

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.

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

 

[Kurt]

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]

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.

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]

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]

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