Forum Discussion
nmaddock
7 years agoHelper I
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.