Skip to main content
cancel
Showing results for 
Search instead 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

Reply
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
DavisBI
Solution Specialist
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.

 

20201027101610.png

(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:

20201027101626.png

 

Mark this post as solution if this helps,thanks!

Davis.Z's blog at LinkedIn)

View solution in original post

2 REPLIES 2
DavisBI
Solution Specialist
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.

 

20201027101610.png

(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:

20201027101626.png

 

Mark this post as solution if this helps,thanks!

Davis.Z's blog at LinkedIn)

Anonymous
Not applicable

Thank you so much for your help!

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

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