cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

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

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?

1 ACCEPTED SOLUTION
Community Champion

@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.

Lima - Peru
2 REPLIES 2
Community Champion

@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.

Lima - Peru
Helper II

Hola Victor,

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.

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!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors