Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
E12345
Resolver II
Resolver II

I need a DAX function to calculate AVG within "Quarter" Group, Defined by "Quarter End Date"

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"). 

Question for Power BI Community on How to Get AVG.jpg

 

If my description on the picture is hard to read, will past it again as text, along with the "table":

Qtr_End_DateTicket_IDMonths_Open
12/31/2023175
12/31/2023286
12/31/2023335
12/31/2023335
12/31/2023335
9/30/2023165
9/30/2023165
9/30/2023285
9/30/2023285
9/30/2023330
9/30/202345
9/30/202345
9/30/2023520
6/30/2023155
6/30/2023155
6/30/2023275
6/30/2023325
6/30/2023325
6/30/20233

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_DateAvg_Months_Open
12/31/202365.3
9/30/202341
6/30/202351.6

 

 

1 ACCEPTED SOLUTION
v-zhouwen-msft
Community Support
Community Support

Hi @E12345 ,

Table data are as follows:

vzhouwenmsft_0-1707275568618.png

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

vzhouwenmsft_1-1707275696491.png

 

vzhouwenmsft_2-1707275706943.png

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.

 

View solution in original post

3 REPLIES 3
E12345
Resolver II
Resolver II

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]

    )

 

Screenshot of the test PBIX project for a Dax function provided by M365 Chat.jpg

 

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:

Screenshot of the test PBIX Comparing Two Totals Depending on DAX Solution by M365 Chat vs. Power BI Community.jpg

 

 

v-zhouwen-msft
Community Support
Community Support

Hi @E12345 ,

Table data are as follows:

vzhouwenmsft_0-1707275568618.png

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

vzhouwenmsft_1-1707275696491.png

 

vzhouwenmsft_2-1707275706943.png

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.

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.