Forum Discussion

rawiswarden's avatar
rawiswarden
Helper I
6 years ago
Solved

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.
 
When I go to each table and filter to this single customer, I do not see $4,100 for this customer in either table.
 
The same is true for mileage.  

 

 

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

Need Help
  • TomMartens's avatar
    TomMartens
    6 years ago

    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

2 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    6 years ago

    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

    • rawiswarden's avatar
      rawiswarden
      Helper I
      6 years ago

      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  .