Hello,
I have lots of energy consumption data in one table. It includes one row per hour, and includes lots of different stores. In another file, I have some information about each store. What I need to do, is to divide the energy data with the area.
I have attached a file with extracted and simplified data to show what I mean.
What I want, is to take the energy from colum C on Energydata sheet, and divide it by the Aren on the Unitdata sheet. To connect the tables in Power BI I will use the Unitname column.That will result in XXX kWh/m2. It need to be applied when using the date hierarchy so that I can look at it by month and year, but also hourly.
I didn't think it would be so hard, bu tI just can't get my head around it.
Thank you!
Hi Mattias,
There's two ways you can do this, either in Power Query before loading to Power Pivot model, or with the USERELATIONSHIP function. See file attached with both solutions.
Mynda
Hi Mynda,
Thank you for your help. I can't really get it to work with the DAX formula. The formula reads:
SpecificEnergy = DIVIDE(SUM(detailed_energy_measurement[raw_value_diff_kwh]),CALCULATE(SUM(Area[Area]),USERELATIONSHIP(detailed_energy_measurement[site_name],Area[SiteName])))
I get the same result in every row, even though the energy consumption varies each hour. The only time it changes, is when the row includes a new unit. Please see attached picture.
Also, I'm not sure that I expressed exactly what I want. I want the energy consumption expressed as kWH/m2, no matter what the time frame is.
So for one hour, that will simply be the the raw_value_diff_kwh/Area. When I'm looking at daily data, that will be the energy for the 24 hours, but still the same area. For one year, it will be 8760hrs, but still the same area.
Maybe it's more complicated than I first thought?
Thanks
Mattias
Hi Mattias,
It sounds like a problem with the relationship.
Regarding this:
"So for one hour, that will simply be the the raw_value_diff_kwh/Area. When I'm looking at daily data, that will be the energy for the 24 hours, but still the same area. For one year, it will be 8760hrs, but still the same area."
You're now quoting fields that weren't in your example file, so I don't know what you're referring to. Does the measure in the file I provided calculate the way you want, or is it wrong too?
Please provide another sample file so I can follow what you're referring to.
Mynda
Hi again,
Yes, I'm sorry it's not the same. But I can't send the real data and power BI due to access rights to the data...
However, I have got it working with a quick measure. I took the Division calculation, and choose the sum of raw_value_diff_kwh as the nominator and Sum of Area as denominator. The only issue I have now, is when I visualize the results. I get the correct values when I pick the site_code from the Area table. But when I take the site_code from the "detailed_energy_measurement" I get strange values. Since I have everything I need in the "detailed_energy_measurement", including the city names, I'd like to use that one for my visualizations. Could it still be something with the relationships?
I have attached a .pdf with as much data I can share. Hopefully that will help you, to help me. Sorry for making it so hard.
Thank you!
Mattias
Hi Mattias,
Thanks for providing the information to help me understand the issue.
The axis/row/column labels should always come from your dimension tables and the value fields from your fact tables. Your measure also correctly supports this because it references the site code area table (the dimension table) for the area value, therefore you must use the site code field from this table in your visuals.
Hope that clarifies things and reassures you that the first visual on page 4 of your PDF is correct because you've used the field from the correct table.
Mynda
Hello,
Thank you for clarifying. After I sent this question I went back and watched the lesson 3.03 again, and that explains it really well.
Have a great weekend.
Mattias
Great to hear, Mattias! Hope you have a lovely weekend too 🙂
