Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |