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
rawiswarden
Helper I
Helper I

Displayed Numbers Do Not Match Source

Hello,

 

I have a data model in Power BI.  I am having an issue that I have zero clue how to debug.  I'm trying to figure out why a few of my measures (in this example, Mileage (Avg) ) do not match the source data.  The calculation is a weighted average below:

 

Mileage (Avg) = calculate(sumx(Facts_PortStats, Facts_PortStats[PortStats.Mileage] * Facts_PortStats[PortStats.DiscountedLoanAmount]) / sumx(Facts_PortStats, Facts_PortStats[PortStats.DiscountedLoanAmount]), userelationship(Facts_PortStats[PortStats.BookedDate], Calendar_Dates[Date]))
 
When I build a table in the report, I get two values for the same metric.
 
Below is the table.  I have unhidden the source data for the measure to figure out what is going on.  I noticed that in the weighted average, $4,100 is being used.
 
Power BI Table.png
When I go to each table and filter to this single customer, I do not see $4,100 for this customer in either table.
Facts_Portstats.png
Dimension_Portstats.png
 
The same is true for mileage.  

 

Facts_mileage.png

dimension_mileage.png

 

Where are the other numbers coming from (158,100 and 4,100)?  What could possibly cause this issue?

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

Hey @rawiswarden ,

 

without having the data, it's almost impossible to guess where the data is coming from (at least for me), but I'm pretty sure that Power BI does not  "invent" data.

Besides that, I would recommend to re-write the measure like this, as it iterates across the table Facts_PortStats just once:

 

Mileage (Avg) = 
	sumx(
		Facts_PortStats
		, calculate(
			divide(
				Facts_PortStats[PortStats.Mileage] 
				* Facts_PortStats[PortStats.DiscountedLoanAmount]
				/ Facts_PortStats[PortStats.DiscountedLoanAmount]
			)
			, userelationship(Facts_PortStats[PortStats.BookedDate], Calendar_Dates[Date])
		)
	)

 

 But maybe this is already some kind of solution.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

2 REPLIES 2
TomMartens
Super User
Super User

Hey @rawiswarden ,

 

without having the data, it's almost impossible to guess where the data is coming from (at least for me), but I'm pretty sure that Power BI does not  "invent" data.

Besides that, I would recommend to re-write the measure like this, as it iterates across the table Facts_PortStats just once:

 

Mileage (Avg) = 
	sumx(
		Facts_PortStats
		, calculate(
			divide(
				Facts_PortStats[PortStats.Mileage] 
				* Facts_PortStats[PortStats.DiscountedLoanAmount]
				/ Facts_PortStats[PortStats.DiscountedLoanAmount]
			)
			, userelationship(Facts_PortStats[PortStats.BookedDate], Calendar_Dates[Date])
		)
	)

 

 But maybe this is already some kind of solution.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Thanks for the tip!  I will use it. 

 

I decided to roll back Power BI file to an older version and the issue is gone away.  I don't know what happened, but a few buggy things happened in my file (M query statements that have worked for months started to fail on refresh, the data mapping was all incorrect for all but a subset of accounts).  Now my file is back to showing the correct data  .

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.