Averagex issue

[Talal141218]

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 values greater than or equal to zero (>= 0).

Furthermore i want to calculate the Average for this Measure ( Durchlauf in Tage) . Unfortunatley i am getting  resulsts don't conrespond with Excel results when i use Averageif. More Screenshots for Avearge in Power BI and in Excel as below: 

Thanks in Advance

[Ray_Minds]

Query : 
Average_Durchlauf_GE_0 =

VAR BaseTable =

 ADDCOLUMNS (

  VALUES ( Fact_SalesLine[SalesLineID] ),

"__Durchlauf", [Durchlauf. in Tagen]

  )

RETURN

  AVERAGEX (

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

 [__Durchlauf]

 )
Result : 

[v-ssriganesh · Accepted solution]

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]

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]

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.

[pcoley]

@Talal141218 Please try with this:

Durchlauf in Tagen =
VAR T =
    FILTER (
        ADDCOLUMNS (
            Fact_SalesLine,
            "@val", [Durchlauf. in Tagen]
        ),
        [@val] >= 0
    )
RETURN
    AVERAGEX (
        T[SalesPiklistDate_NK],
        [@val]
    )

I hope this helps. If so please mark it as a solution. Kudos are welcome!

[Talal141218]

Thanks for your answer, but unfortuntely did't work. I want Average for a Measure "Durch laufzeit in Tagen"

[GeraldGEmerick]

@Talal141218 One issue I see is that you are using SUMMARIZE in your AVERAGEX function and also adding a column using CALCULATE within the SUMMARIZE. There is an article out there somewhere from SQLBI that says not to do that and instead use ADDCOLUMNS. Not sure if that is your issue but with what you are doing you can get wonky results.

[Talal141218]

@GeraldGEmerick  Thanks for your advice, i tried without them and easy Funktions with Average and Filter, but also didn't work.