Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi!
I need a DAX function to calculate AVG number of "Months_Open" within "Qtr_End_Date" Group.
I will add my data sample and the result I expect as attachment, to assist you in understanding of the issue.
Please see the description below - and thank you!
(I am assuming i need a DAX function that creates a temporary table with unique IDs within each "Quarter", and then takes the average of "Months_Open").
If my description on the picture is hard to read, will past it again as text, along with the "table":
Qtr_End_Date | Ticket_ID | Months_Open |
12/31/2023 | 1 | 75 |
12/31/2023 | 2 | 86 |
12/31/2023 | 3 | 35 |
12/31/2023 | 3 | 35 |
12/31/2023 | 3 | 35 |
9/30/2023 | 1 | 65 |
9/30/2023 | 1 | 65 |
9/30/2023 | 2 | 85 |
9/30/2023 | 2 | 85 |
9/30/2023 | 3 | 30 |
9/30/2023 | 4 | 5 |
9/30/2023 | 4 | 5 |
9/30/2023 | 5 | 20 |
6/30/2023 | 1 | 55 |
6/30/2023 | 1 | 55 |
6/30/2023 | 2 | 75 |
6/30/2023 | 3 | 25 |
6/30/2023 | 3 | 25 |
6/30/2023 | 3 | 25 |
Please note that I need to make sure I am only counting "Unique (distinct)" Ticket IDs.
For Example, the Qtr_End_Date Group that ends in 12/31/2023 (highlighted in blue) has three unique "Ticket_ID"s, whose "Months_Open Average would be
(75 + 86 + 35) / 3 = 65.3
Similarly,
the Qtr_End_Date Group that ends in 9/30//2023 (highlighted in green) has five unique "Ticket_ID"s, whose "Months_Open Average would be
(65 + 85 + 30 + 5 + 20) / 5 = 41
he Qtr_End_Date Group that ends in 6/30//2023 (highlighted in yellow) has three unique "Ticket_ID"s, whose "Months_Open Average would be
(55 + 75 + 25) / 3 = 51.6
The result I expect:
Qtr_End_Date | Avg_Months_Open |
12/31/2023 | 65.3 |
9/30/2023 | 41 |
6/30/2023 | 51.6 |
Solved! Go to Solution.
Hi @E12345 ,
Table data are as follows:
Please follow these steps:
1. Use the following DAX expression to create a measure named 'Denominator'
Denominator = COUNTROWS(VALUES(Tabelle1[Ticket_ID]))
2. Use the following DAX expression to create a measure named 'Numerator'
Numerator = SUMX(VALUES(Tabelle1[Months_Open]),'Tabelle1'[Months_Open])
3. Use the following DAX expression to create a measure named 'Avg_Months_Open'
Avg_Months_Open = DIVIDE([Numerator],[Denominator])
4. Final output
Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi E12345 (Wenbin Zhou),
Thank you so much for the solution, it worked perfectly! 😊
I have a bit of the good news myself - I actually logged in to submit the "answer", as I was able to use Microsoft Copilot 365 to solve this problem which provided the same results, but using a single DAX function.
You definitely deserve a credit for your solution (many thanks for letting me learn from it as well!), but I will share the Dax Function provided by M365 Chat (Microsoft Copilot) here just as well.
I am now curious to test both solutions with the "Optimizer" to see which one is faster ;).
Here is the alternative solution using the single DAX formula that produced the same result:
Average Months Open =
AVERAGEX(
SUMMARIZE(
TableName,
TableName[Qtr_End_Date],
TableName[Ticket_ID],
"AvgMonthsOpen", AVERAGE(TableName[Months_Open])
),
[AvgMonthsOpen]
)
However, I have a question - I just compared the two soilutions (please see the screenshot below), and although both provide the same result per Quarter, the "Total" AVG per chart is different on both charts.
I would like to show the total per all three quarters to be displayed as the AVG of the Averages of each of the three Quarters. Which I am assuming should be (65.33 + 51.67 + 41) / 3 = 158/3 = 52.6
Neither result shows this AVG, but the "single DAX" solution comes close...
Please see screenshot below with both solutions:
Hi @E12345 ,
Table data are as follows:
Please follow these steps:
1. Use the following DAX expression to create a measure named 'Denominator'
Denominator = COUNTROWS(VALUES(Tabelle1[Ticket_ID]))
2. Use the following DAX expression to create a measure named 'Numerator'
Numerator = SUMX(VALUES(Tabelle1[Months_Open]),'Tabelle1'[Months_Open])
3. Use the following DAX expression to create a measure named 'Avg_Months_Open'
Avg_Months_Open = DIVIDE([Numerator],[Denominator])
4. Final output
Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
123 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
183 | |
92 | |
67 | |
62 | |
52 |