Forum Discussion

nmaddock's avatar
nmaddock
Helper I
7 years ago

Power Bi Measure giving me a different result to SQL Query

Hi,

 

Im trying to create a new Measure to calculate the kgm3 for our industry types based on jobs that we have done. I have written the query in SQL but the Power BI measure is giving me a different result, the sql query is:

 

 

SELECT IndustryType.Name, 
	SUM(WeighData.Weight) as Weight, 
	SUM(ContainerType.Capacity) as Volume, 
	SUM(WeighData.Weight) / (SUM(ContainerType.Capacity)/1000) as Kgm3  
  FROM  Customer Customer
  INNER JOIN Site Site on Site.CustomerId = Customer.Id
  INNER JOIN Agreement Agreement on Agreement.SiteId = Site.Id
  INNER JOIN ContainerType ContainerType on ContainerType.Id = Agreement.ContainerTypeId
  INNER JOIN IndustryType IndustryType on IndustryType.Id = Site.IndustryTypeId 
  INNER JOIN Job Job on Job.AgreementId = Agreement.Id
  INNER JOIN WeighData WeighData on WeighData.JobId = job.Id
  where job.RecordCreationDate > '2019-01-01'
  group by IndustryType.Name

The results from sql is:

 

 

The power Bi Measure for Weight is correct but the volume is way off.

Here is my model diagram, the relationships Cross filter direction is set to both

 

My Measure are:

Total Volume = SUM(ContainerType[Capacity])
Total Weight = SUM(WeighData[Weight])
kg/m3 = [Total Weight] / ([Total Volume] / 1000)
 
I have also applied the same date filter, any help i would much appreciate as I am very new to Power BI so I could be missing something really obvious. 

 

1 Reply

  • nmaddock can you possibly share pbix file wiht sample data, t is bit of complex by looking at relationship diagram. remove any sensitive data before sharing.