Forum Discussion

Talal141218's avatar
Talal141218
Helper III
6 months ago
Solved

Averagex issue

Hello Profis,    I am working with a measure called [Durchlauf. in Tagen], which calculates the number of days between different date fields. I attempted to compute an average that includes only va...
  • v-ssriganesh's avatar
    v-ssriganesh
    6 months ago

    Hi Talal141218,
    Thank you for posting your query in the Microsoft Fabric Community Forum.
    I’ve reproduced your scenario in Power BI Desktop using my sample data aligned with your table structure.

    To match Excel AVERAGEIF(>=0), I used the following approach:

    • Base measure: [Durchlauf. in Tagen] (row-level day difference)
    Durchlauf. in Tagen =

VAR PickDate =

    SELECTEDVALUE ( Fact_SalesLine[SalesPicklistDate] )

VAR ShipDate =

    SELECTEDVALUE ( Fact_SalesLine[SalesLineShippingDateRequested] )

RETURN

IF (

    NOT ISBLANK ( PickDate )

        && NOT ISBLANK ( ShipDate ),

    DATEDIFF ( PickDate, ShipDate, DAY )
)

     

    • Final measure: Average_Durchlauf_GE_0, which materializes the row-level values and calculates the average only for values ≥ 0 using AVERAGEX
    Average_Durchlauf_GE_0 =

VAR BaseTable =

    ADDCOLUMNS (

        VALUES ( Fact_SalesLine[SalesLineID] ),

        "__Durchlauf", [Durchlauf. in Tagen]

    )

RETURN

AVERAGEX (

    FILTER ( BaseTable, [__Durchlauf] >= 0 ),

    [__Durchlauf]
)

    For your reference, I’m attaching the .pbix file so you can review the complete implementation.
    Thanks, pcoley & GeraldGEmerick for sharing valuable insights.



    Best regards,
    Ganesh Singamshetty.

v-ssriganesh's avatar
v-ssriganesh
Community Support
6 months ago

Hi Talal141218,
Thank you for posting your query in the Microsoft Fabric Community Forum.
I’ve reproduced your scenario in Power BI Desktop using my sample data aligned with your table structure.

To match Excel AVERAGEIF(>=0), I used the following approach:

  • Base measure: [Durchlauf. in Tagen] (row-level day difference)
Durchlauf. in Tagen =

VAR PickDate =

    SELECTEDVALUE ( Fact_SalesLine[SalesPicklistDate] )

VAR ShipDate =

    SELECTEDVALUE ( Fact_SalesLine[SalesLineShippingDateRequested] )

RETURN

IF (

    NOT ISBLANK ( PickDate )

        && NOT ISBLANK ( ShipDate ),

    DATEDIFF ( PickDate, ShipDate, DAY )
)

 

  • Final measure: Average_Durchlauf_GE_0, which materializes the row-level values and calculates the average only for values ≥ 0 using AVERAGEX
Average_Durchlauf_GE_0 =

VAR BaseTable =

    ADDCOLUMNS (

        VALUES ( Fact_SalesLine[SalesLineID] ),

        "__Durchlauf", [Durchlauf. in Tagen]

    )

RETURN

AVERAGEX (

    FILTER ( BaseTable, [__Durchlauf] >= 0 ),

    [__Durchlauf]
)

For your reference, I’m attaching the .pbix file so you can review the complete implementation.
Thanks, pcoley & GeraldGEmerick for sharing valuable insights.



Best regards,
Ganesh Singamshetty.

Talal141218's avatar
Talal141218
Helper III
6 months ago

Hi, 

Thanks for your Support. As you see in the Screenshot. I did't get in your formula the Total of Average. Could you please help me in this topic. Best wishes. 

 

  • v-ssriganesh's avatar
    v-ssriganesh
    Community Support
    6 months ago

    Hi Talal141218,
    In my PBIX, the Total value is returned correctly using the same measure.

    It’s possible that the difference you’re seeing is related to the data structure or the fields used in the visual. Could you please share a small sample of your data so I can reproduce it on my end and verify the behavior.

    Need help uploading data? How to provide sample data in the Power BI Forum - Microsoft Fabric Community

    Thank you, and I’ll be happy to check this further.