Forum Discussion

Sanko's avatar
Sanko
New Member
3 years ago
Solved

Gas consumption with missing readings

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:

max_meter = MAXA(Gas_meters[gas_meter_value])
min_meter = MINA(Gas_meters[gas_meter_value])
consumption = [max_meter]-[min_meter]

 

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

 

Need Help
  • lukiz84's avatar
    lukiz84
    3 years ago

    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

2 Replies

  • lukiz84's avatar
    lukiz84
    Icon for Memorable Member rankMemorable Member
    3 years ago

    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