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
hemal_kanjia
Helper I
Helper I

Calculate Quarterly Average of measure

 

Capture.JPG

 

Hello all,

 

I have an excel data shown above where Percentage is the measure which i have calculated in Power Bi.

Now, I want to calculate quarterly average of percentage column.

Is there any way to calculate this ?

 

Right now, this is the one year of all the data; I have many years data and i want to calculate average accordingly.

 

I have not Date Dimension table, only one date column shown above(First Date of the month).

 

I saw other post of this forum ragarding this but they have used Date Key table; but i don't have that.

 

Please help me to get out from this.

 

 

Thanks in advance.

1 ACCEPTED SOLUTION
v-huizhn-msft
Microsoft Employee
Microsoft Employee

Hi @hemal_kanjia,

In your resource table, please create a calculated column to get quarter.

quarter = FORMAT(Test[Date],"Q")


Please see the screenshot below, which shows my sample table and calculated column result.

1.PNG

Then create the percentage and average by creating measures. using the formulas below.

Percentage = DIVIDE(SUM(Test[value]),SUMX(ALL(Test),Test[value]))

Avg% = AVERAGEX(ALLEXCEPT(Test,Test[quarter]),Test[Percentage])


Fianlly, I create a table visual, select the date, quarter and two measure to disply as follows.

2.PNG

Best Regards,
Angelia

View solution in original post

4 REPLIES 4
v-huizhn-msft
Microsoft Employee
Microsoft Employee

Hi @hemal_kanjia,

In your resource table, please create a calculated column to get quarter.

quarter = FORMAT(Test[Date],"Q")


Please see the screenshot below, which shows my sample table and calculated column result.

1.PNG

Then create the percentage and average by creating measures. using the formulas below.

Percentage = DIVIDE(SUM(Test[value]),SUMX(ALL(Test),Test[value]))

Avg% = AVERAGEX(ALLEXCEPT(Test,Test[quarter]),Test[Percentage])


Fianlly, I create a table visual, select the date, quarter and two measure to disply as follows.

2.PNG

Best Regards,
Angelia

Anonymous
Not applicable

Hi, @v-huizhn-msft , thanks. that's a geat help. I am able to follow through the steps. However, I have a DateTable and had active relatinship wit the test table. This Average DAX:

Avg% = AVERAGEX(ALLEXCEPT('Date', 'Date'[Quarter]), [Percentage])
 
The data is:
Table testTable test
The date table is:
DateTable.PNG
Meausre:
Percentage = DIVIDE(SUM(Test[value]),SUMX(ALL(Test),Test[value]))

Appreciate any help, thanks.
 
Rgds...Aziz

Can we use the Medianx function similar to Averagex?

 

Avg% = AVERAGEX(ALLEXCEPT(Test,Test[quarter]),Test[Percentage])

 

Median = MedianX(ALLEXCEPT(Test,Test[quarter]),Test[Percentage])
SivaMani
Resident Rockstar
Resident Rockstar

@hemal_kanjia,

 

Try this,

 

Avg % = CALCULATE(AVERAGE(Table1[Percentage]),FILTER(Table1,ENDOFQUARTER(Table1[Date])))

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.