Averaging averages by Date and then Product in variable

[samc_26]

So I have three columns, a date, product and value column and I've got an average for these costs monthly but BI is adding up the average by product first and then averaging those averages across the date. What I want is to calculate an average by date and then an average of those averages across the products. I tried to write out some variables with the usual calculate(average(Table[Value]), filter(Table[Product] = "Book")) and then repeat this for each product to then calculate these averages but it keeps telling me the 'syntax for ')' is incorrect and I don't know what that means 😞 

If anyone can understand my explanation and work this out I would be grateful! Thank you!

[v-sdhruv]

Hi @samc_26 ,
Since we didnt hear back, we would be closing this thread.
If you need any assistance, feel free to reach out by creating a new post.

Thank you for using Microsoft Community Forum

[v-sdhruv]

Hi @samc_26 ,

Can you please provide your full DAX measure so that we can help you modify to achieve the right solution.
Thank You

[v-sdhruv]

Hi @samc_26 ,

Can you please provide your full DAX measure so that we can help you modify to achieve the right solution.
Additionally, you might want to check how AverageX function works
AVERAGEX function (DAX) - DAX | Microsoft Learn
The AVERAGEX function can take  its argument an expression that is evaluated for each row in a table. This enables you to perform calculations and then take the average of the calculated values.

Hope this helps!

[Shahid12523 · Accepted solution]

To average by Date first, then average those across Products, use this pattern:
AverageByDateThenProduct :=
AVERAGEX(
VALUES(SalesData[Date]),
CALCULATE(AVERAGE(SalesData[Value]))
)

This gives you the average of daily averages, not the average of all values. If you want to do this per product, wrap it in another AVERAGEX over VALUES(SalesData[Product]).

[FBergamaschi]

Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Alternatively, you can share your .pbix via some cloud service and paste the link here. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

Need help uploading data? click here

Want faster answers? click here