Forum Discussion
Averagex issue
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
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.
8 Replies
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.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.
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.
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.
GeraldGEmerick Thanks for your advice, i tried without them and easy Funktions with Average and Filter, but also didn't work.
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!
Thanks for your answer, but unfortuntely did't work. I want Average for a Measure "Durch laufzeit in Tagen"
Query :
Average_Durchlauf_GE_0 =VAR BaseTable =
ADDCOLUMNS (
VALUES ( Fact_SalesLine[SalesLineID] ),
"__Durchlauf", [Durchlauf. in Tagen]
)
RETURN
AVERAGEX (
FILTER ( BaseTable, [__Durchlauf] >= 0 ),
[__Durchlauf]
)
Result :
