Why is the measure multiplied by 7 when there is only one record in the table

[fiveone]

My fact table includes the hourly rate of resources.

Most of the data is at the week level (40 hours per week).

the aggregation function for this measure should be 

Total Revenue = SUM('Rate by Week Endings'[Rate])*40

I had issue with this so I precalculated this with powerquery and called it [Weekly Revenue]).

But I still get the same behaviour, where, although there is a single record (when I filter on given week and resource), the following measure still gives 7 times what is expected

Total Revenue = SUM('Rate by Week Endings'[Weekly Revenue])

The screenshot below shows that

• there is a single record
• all dimensions of the table are included on the grid
• the max measure shows 4,900 (=weekly revenue)
• the count metric shows 7
• the total metric shows 34,300 which is consistent with 4900x7

Maybe the record is duplicated 7 times, but then why does not show when every single dimensions/column is included on the grid?

Where can that behaviour come from?

[Vvelarde · Accepted solution]

@fiveone

i'll made a double check of:

Are only 1 record by Week? or 7 (days of week)

Left in the table the dimensions and Rate 2016 ..select don't summarize (Rate 2016). Still showing 1 record? If answer is yes you have identical rows in all the columns that are you using.

[fiveone]

Hola Victor, 

Thanks for your quick reply!

I followed your advice and left in the table only the dimensions and Rate 2016 . I selected don't summarize (Rate 2016),  and yes I only see 1 records. 

Your point about identical rows in all the columns made a lot of sense in terms of why I would only see 1 record and still get a count of 7.

So I went ahead and added a step to delete duplicated records.

and TADAM problem solved!

This probably came from the merge step (left join) with the Calendar table that I recently added.

I guess what made it confusing is that because of how the data is sorted in PowerQuery data viewer, the duplicated rows didn't show. But now that I know the problem, I can scroll down and see the duplication very clearly.

Thank you so much amigo for putting me on the righ track. You rock!