dax forum

2 Topics

DAX averages in a matrix
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.
Solved
ReginaP
1 year ago Place DAX Commands and Tips
Views
0likes
Comments
customized financial quarter in new table
Hi, I am trying to create the customized financial quarter. So how to change the current format from "4Q 2020" to "Q4 2020" in the Quarter year column. Formula used: monthTable = var FullCalendar = ADDCOLUMNS(CALENDAR("2020/10/1",today()),"Month Number",MONTH([Date]),"Year",YEAR([Date]),"Year-Month",LEFT(FORMAT([Date],"yyyyMM"),6),"Year-MonthName",Format([Date],"MMMM") & " " & YEAR([Date]),"Quarter",if(MONTH([Date])<4,"Q1",IF(MONTH([DATE])<7,"Q2",IF(MONTH([DATE])<10,"Q3","Q4"))),"Quarter Year",FORMAT([Date],"Q""Q") & " " & YEAR([Date])) return SUMMARIZE(FullCalendar,[Month Number],[Year],[Year-Month],[Year-MonthName],[Quarter],[Quarter Year]) Thanks in advance!! Best Regards, Arul
Solved
Anonymous
4 years ago Place DAX Commands and Tips
Views
0likes
Comments