Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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.
Solved! Go to Solution.
Assuming you are using table 1, try creating the below measure
Average per Quarer = CALCULATE(SUM(Table1[Bonus])/3,ALLEXCEPT(table1,Table1[Quarter]))
Assuming you are using table 1, try creating the below measure
Average per Quarer = CALCULATE(SUM(Table1[Bonus])/3,ALLEXCEPT(table1,Table1[Quarter]))
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
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)
I used this formula as calculated column and got this table , which is not I wanted to get 😕
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)
Hi @Omega,
thanks, I understad now.
Any idea how to extend those average values into months wihtout Bonus ?
We can also assume that there is time table with Months and Quarters as dimension.
that works, thank you.
User | Count |
---|---|
120 | |
65 | |
62 | |
56 | |
50 |
User | Count |
---|---|
181 | |
85 | |
69 | |
62 | |
55 |