cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Anonymous
Not applicable

## Average of a measure

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.

1 ACCEPTED SOLUTION
Solution Specialist

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!

2 REPLIES 2
Solution Specialist

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!

Anonymous
Not applicable

Thank you so much for your help!

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors