Calculate Quarterly Average of measure

[hemal_kanjia]

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.

[v-huizhn-msft · Accepted solution]

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.



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.



Best Regards,
Angelia

[Anonymous]

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 test

The date table is:

Meausre:

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

Appreciate any help, thanks.
 

Rgds...Aziz

[chinthada]

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]

@hemal_kanjia,

Try this,

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