Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I'm quite new to power BI and I have to calculate gas consumption over a period, but there are holes in the data, like this (all gas meter has a row at the end of the month, but sometimes there is no reading):
Because of these missing data, the calculation of consumption (or at least something close to it) shold be the maximum reading minus the minimum reding of a period. I created the following measures:
The problem is, that on gas_meter_id level the calculated consumption is correct, but higher levels (city or county summary) it's not, because it calculates the min and max value of that level, not the aggregated sum of the gas meters consumptions:
County A consumption should be 10+25+13=48 not 46, city A2 consumption should be 25+13=38 not 28.
How can I show the correct county and city values?
Thank you in advance !
Sanko
Solved! Go to Solution.
Hi,
you could add a calculated column to your table, something like:
PreviousMeter := CALCULATE(
MAX(Gas_meters[gas_meter_value]),
FILTER(
'Gas_meters',
'Gas_meters'[date] < EARLIER('Gas_meters'[date]) &&
'Gas_meters'[county] = EARLIER('Gas_meters'[county]) &&
'Gas_meters'[city] = EARLIER('Gas_meters'[city]) &&
'Gas_meters'[gas_meter_id] = EARLIER('Gas_meters'[gas_meter_id])
)
)
And add another calculated column
GasConsumption :=
IF(
'Gas_meters'[gas_meter_value] > 0,
'Gas_meters'[gas_meter_value] - 'Gas_meters'[PreviousMeter]
)
Now you can just add a measure
Gas consumption = SUM('Gas_meters'[GasConsumption])
and don't have to worry in any constellation. It just takes a little longer to refresh data (depending on the size of the original table). But I do something similar with >200k rows and it just takes seconds
BR
Thank you, it works!
Sanko
Hi,
you could add a calculated column to your table, something like:
PreviousMeter := CALCULATE(
MAX(Gas_meters[gas_meter_value]),
FILTER(
'Gas_meters',
'Gas_meters'[date] < EARLIER('Gas_meters'[date]) &&
'Gas_meters'[county] = EARLIER('Gas_meters'[county]) &&
'Gas_meters'[city] = EARLIER('Gas_meters'[city]) &&
'Gas_meters'[gas_meter_id] = EARLIER('Gas_meters'[gas_meter_id])
)
)
And add another calculated column
GasConsumption :=
IF(
'Gas_meters'[gas_meter_value] > 0,
'Gas_meters'[gas_meter_value] - 'Gas_meters'[PreviousMeter]
)
Now you can just add a measure
Gas consumption = SUM('Gas_meters'[GasConsumption])
and don't have to worry in any constellation. It just takes a little longer to refresh data (depending on the size of the original table). But I do something similar with >200k rows and it just takes seconds
BR
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
94 | |
82 | |
70 | |
64 |
User | Count |
---|---|
116 | |
106 | |
94 | |
79 | |
72 |