cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper V

Average per month in quarter

HI,

I am looking for DAX solution for this calculation:

 Quarter Month Bonus 2018Q1 Jan-18 36000 2018Q1 Feb-18 2018Q1 Mar-18 2018Q2 Apr-18 27000 2018Q2 May-18 2018Q2 Jun-18 2018Q3 Jul-18 42000 2018Q3 Aug-18 2018Q3 Sep-18 2018Q4 Oct-18 24000 2018Q4 Nov-18 2018Q4 Dec-18

I would like to create a measure that will all me to get something like this:

 Quarter Month Bonus Average per month 2018Q1 Jan-18 36000 12000 2018Q1 Feb-18 12000 2018Q1 Mar-18 12000 2018Q2 Apr-18 27000 9000 2018Q2 May-18 9000 2018Q2 Jun-18 9000 2018Q3 Jul-18 42000 14000 2018Q3 Aug-18 14000 2018Q3 Sep-18 14000 2018Q4 Oct-18 24000 8000 2018Q4 Nov-18 8000 2018Q4 Dec-18 8000

I will be grateful for help with that.

1 ACCEPTED SOLUTION
Impactful Individual

Assuming you are using table 1, try creating the below measure

`Average per Quarer = CALCULATE(SUM(Table1[Bonus])/3,ALLEXCEPT(table1,Table1[Quarter]))`

8 REPLIES 8
Impactful Individual

Assuming you are using table 1, try creating the below measure

`Average per Quarer = CALCULATE(SUM(Table1[Bonus])/3,ALLEXCEPT(table1,Table1[Quarter]))`

Helper V

Hi @Omega,

This measure works only when measuer is sliced by quarter dimension .

I have one more question, how can I create a calculated column where all those bonuses will be presented by month ?

I have a salary table where each row corresponds to each month and would like to have column with bonuses calcualted as mentioned.

Or maybe new measure where in that table above the column with qurter will not be presented .

I would like to get something like this :

 Month Bonus Average per month Jan-18 36000 12000 Feb-18 12000 Mar-18 12000 Apr-18 27000 9000 May-18 9000 Jun-18 9000 Jul-18 42000 14000 Aug-18 14000 Sep-18 14000 Oct-18 24000 8000 Nov-18 8000 Dec-18 8000

Marcin

Impactful Individual

I simplified the formula. The challenge will be that all months that don't have bonuses will have zero. Power BI supresses blanks 😞

`Average = IF(ISBLANK(SUM(Table1[Bonus])),0,SUM(Table1[Bonus])/3)`
Helper V

I used this formula as calculated column and got this table , which is not I wanted to get 😕

Impactful Individual

The formula in my previous post was used as a measure. If you want to use a column, try:

`Average  = IF(ISBLANK(Table1[Bonus]),BLANK(),Table1[Bonus]/3)`
Helper V

Hi @Omega,

Any idea how to extend those average values into months wihtout Bonus ?

Helper V

We can also assume that there is time table with Months and Quarters as dimension.

Helper V

that works, thank you.

Announcements

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors