cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Calculate Quarterly Average of measure

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.

1 ACCEPTED SOLUTION
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.

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

4 REPLIES 4
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.

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
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 test
The date table is:

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

Appreciate any help, thanks.

Rgds...Aziz
Helper I

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])`
Resident Rockstar

Try this,

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors