Average of a measure

[Anonymous]

Hello!

I'm trying to calculate an average of another measure. So i have "Mileage_value" column that shows how many kilometres a driver has driven and another column "Fuelconsumption_value" that shows how much fuel has been used. 

First i calculated how many kilometres a driver has driven.

Kilometres driven =

VAR minimi = MIN(CombinedTable[Mileage_Value_Kilometre]) VAR maksimi = MAX(CombinedTable[Mileage_Value_Kilometre]) return IF( ISFILTERED('CombinedTable'[vehicle_id]) || ISFILTERED('CombinedTable'[Date]); (maksimi - minimi) ; BLANK() )

Calculation for the fuel consumption.

Blank measure FuelConsumption =

VAR minimi = MIN(CombinedTable[fuelconsumption_value]) VAR maksimi = MAX(CombinedTable[fuelconsumption_value]) return IF( ISFILTERED('CombinedTable'[vehicle_id]) || ISFILTERED('CombinedTable'[Date]); maksimi - minimi ; BLANK() )

Here is my calculation for litres per 100km.

Consumption(L/100km) = 100 * [Blank measure FuelConsumption] / [Kilometres driven]

Now i'm trying to calculate the average litres per 100km of the whole table so it shows which driver's are under and over the company average and i don't know how to form a correct DAX syntax for it.

[DavisBI · Accepted solution]

Hi @Anonymous  ,

It cost my time to simulate your dataset. 

The key to this problem is that you have to have a column to identify the start and end points, you can see that I have added a new field below - status.

(Of course, this field can also be directly calculated using DAX, but if you can add this column to the data source, any way to consume memory is not recommended)

Then you can use the following DAX formula.

Consumption(L/100km) Avg =
VAR _S_KM =
    CALCULATE (
        SUM ( Sheet2[KM] ),
        FILTER ( ALLSELECTED ( 'Sheet2' ), 'Sheet2'[STATUS] = "START" )
    )
VAR _E_KM =
    CALCULATE (
        SUM ( Sheet2[KM] ),
        FILTER ( ALLSELECTED ( 'Sheet2' ), 'Sheet2'[STATUS] = "END" )
    )
VAR _S_FUEL =
    CALCULATE (
        SUM ( Sheet2[FUEL] ),
        FILTER ( ALLSELECTED ( 'Sheet2' ), 'Sheet2'[STATUS] = "START" )
    )
VAR _E_FUEL =
    CALCULATE (
        SUM ( Sheet2[FUEL] ),
        FILTER ( ALLSELECTED ( 'Sheet2' ), 'Sheet2'[STATUS] = "END" )
    )
RETURN
    DIVIDE ( _E_FUEL - _S_FUEL, _E_KM - _S_KM ) * 100

The results are shown in the below figure, it works perfectly:

Mark this post as solution if this helps,thanks!

( Davis.Z's blog at LinkedIn)

[Anonymous]

Thank you so much for your help!