DAX averages in a matrix

[ReginaP]

Hello,
I have been working on a Power BI report that calculates weighted averages across business locations and location groups in a matrix. The location group and location columns are placed in the row section. While the measure I’m currently using accurately calculates the location averages, the location group averages are incorrect.

The following measure returns the correct location averages:
DAX Setup Days = CALCULATE( AVERAGE('CGM'[DeliveryDays]), KEEPFILTERS('CGM'[Deliverydate] >= DATE(2025, 3, 1)), KEEPFILTERS('CGM'[Deliverydate] <= DATE(2026, 2, 28)) )

The averages and number of deliveries for each location are as follows:
- Location 1: Average = 36, Deliveries = 1
- Location 2: Average = 17.03, Deliveries = 34
- Location 3: Average = 16.50, Deliveries = 14
- Location 4: Average = 17.86, Deliveries = 29

The correct average for the location group is 17.49, which I verified in an Excel file. There was also an additional location with a zero average included in the Excel. However, my measure returned an average of 16.95 for the location group.

I would appreciate any help you can provide to resolve this issue.

[samratpbi · Accepted solution]

Hi, can you please provide some sample data and what calculation you are trying to achieve? Also I can see there is hard coded date in your measure? Is it expected? may be you can make it dynamic.

Thanks

[FBergamaschi]

Hi @ReginaP ,

as @samratpbi asked, please provide data sample (in a usable format, not an image) and images of the issue + snapsho of the data model.

IN any case, the issue seems due to the granularity of your iternations, we need to fix that in the DAX expression

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[v-sgandrathi]

Hi @ReginaP,

Can you please confirm whether you have resolved issue. If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. This will be helpful for other community members who have similar problems to solve it faster.

[v-sgandrathi]

Hi @ReginaP,

I wanted to check if you had the opportunity to follow up on our previous conversation. If yes can you please provide the sample data so that we can provide you with the accurate and correct solution.

Please feel free to contact us if you have any further questions. 

Thank you.

[v-sgandrathi]

Hi @ReginaP,

We’re following up to check whether you were able to look into our earlier discussion. If you're still facing challenges, sharing a sample dataset will enable us to provide a focused and effective solution.

We’re here to help if you have any additional questions.

Thank you.