Summing Values for Unique Records

[MD610]

I am new to PowerPivot and have already discovered how it will be changing my work life forever. However, I am still learning the more complex patterns of DAX syntax and have come across a situation that is stumping me.

I have a table of employee records joined to a table of work sites and then to a table of dates. IT has created this tjoined view in SQL Server. The final result is a highly denormalized view of the data.

The employee records have hours and wages associated with them. Due to the denormalization, each employee record appears multiple times in my data. I would like to create a measure that sums the unique hours and wages values for each unique Employee ID.

So far I have either ended up with errors or the sum of all the duplicate wages in my denormalized data.

So for example, if I have an employee record has 40 hours and it appears 10 times in my denormalized data, I don't want to end up with 400hrs in my measure. I just need the 40 summed once based on the unique Employee ID.

Thanks in advance for any tips!

 

[powerpivotpro]

Have you tried taking the SUM and then dividing by COUNTROWS(DISTINCT(YourTable[CustomerID]))?

Also, I highly recommend requesting that the DB folks deliver you the data as separate views so you can represent these as multiple tables in PowerPivot. There are many advantages to that, some of which are covered here:

http://www.powerpivotpro.com/2011/08/less-columns-more-rows-more-speed/

And some others are covered here in this two-part series:

http://www.powerpivotpro.com/2012/01/salesbudget-integrating-data-of-different-grains/

http://www.powerpivotpro.com/2012/01/data-of-different-grains-a-followup/

 

[MD610]

I did try that and was getting the correct value sometimes but not when certain filters were applied.

I will play around with it a bit more. I was on vacation for the past week and hadn't gotten a chance to go back and look at this closely again.

BTW, I really like your blog. Its been very useful to me in learning PowerPivot. Haven't gotten thru all the posts yet but I will definitely look at the ones you listed on the topic of utilizing multiple tables. I was using a combined view that IT already had in place for some legacy reporting. I will speak with them or look at breaking up the data on my own. Thanks!

 

[buclao]

I am new to PowerPivot and have already discovered how it will be changing my work life forever. However, I am still learning the more complex patterns of DAX syntax and have come across a situation that is stumping me.

I have a table of employee records joined to a table of work sites and then to a table of dates. IT has created this tjoined view in SQL Server. The final result is a highly denormalized view of the data.

The employee records have hours and wages associated with them. Due to the denormalization, each employee record appears multiple times in my data. I would like to create a measure that sums the unique hours and wages values for each unique Employee ID.

So far I have either ended up with errors or the sum of all the duplicate wages in my denormalized data.

So for example, if I have an employee record has 40 hours and it appears 10 times in my denormalized data, I don't want to end up with 400hrs in my measure. I just need the 40 summed once based on the unique Employee ID.

Thanks in advance for any tips!

Try using the SUMMARIZE function if you are using the latest version of Powerpivot, it might help you but it depends on how your data looks like (not sure if you have records by how many hours he did in the NSP or the ESP or something like that or just a total of the whole hours).

Def agree with the comment from Powerpivotpro, a separate catalogue table with just the distinct names and id's of the employees would help you A LOT and will make your life easier, but if they don't give you that query then you might want to use the SUMMARIZE function.

Hope this helps

 

[MD610]

I know enough SQL that I was able to create my own table of unique records. That was by far the easiest solution to this dilemma.

I don't have the latest version of PowerPivot yet because I am still waiting for my company to upgrade to the latest version of SharePoint. Thanks to my pushing, we are scheduled to do so in the next few months. So for the time being, we are sharing and viewing these the "old" way. Until then I will be using the first version of PowerPivot I downloaded because I had to get a bunch of other people set up with it as well to view these files. I read that files created in the latest version can not be read by version 1 and I don't feel like upgrading everyone again.

Thanks for the tips!