Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
fiveone
Helper II
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

2017-02-03 15_08_15-Staffing Dashboard - Power BI Desktop.png

 

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
Vvelarde
Community Champion
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

View solution in original post

2 REPLIES 2
Vvelarde
Community Champion
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

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!

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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