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.
Hello!
I'm calculating the average growth rates of some sale values for each quarter. I had to convert the quarters into numeric values to get it to work though.
Here is the data:
Sales | Quarter | Qlabel |
454 | 4 | Q4 2022 |
326 | 4 | Q4 2022 |
542 | 3 | Q3 2022 |
542 | 4 | Q4 2022 |
653 | 3 | Q3 2022 |
553 | 2 | Q2 2022 |
535 | 2 | Q2 2022 |
554 | 2 | Q2 2022 |
553 | 1 | Q1 2022 |
985 | 4 | Q4 2022 |
696 | 4 | Q4 2022 |
662 | 4 | Q4 2022 |
366 | 3 | Q3 2022 |
365 | 1 | Q1 2022 |
665 | 4 | Q4 2022 |
363 | 3 | Q3 2022 |
352 | 1 | Q1 2022 |
233 | 4 | Q4 2022 |
352 | 1 | Q1 2022 |
524 | 2 | Q2 2022 |
235 | 4 | Q4 2022 |
241 | 3 | Q3 2022 |
545 | 1 | Q1 2022 |
658 | 3 | Q3 2022 |
654 | 4 | Q4 2022 |
354 | 3 | Q3 2022 |
365 | 4 | Q4 2022 |
1000 | 1 | Q1 2022 |
545 | 3 | Q3 2022 |
985 | 2 | Q2 2022 |
654 | 1 | Q1 2022 |
354 | 1 | Q1 2022 |
When I turn it into a visualisation it shows the quarter as a number (1 instead of Q1 2022). Is there any way of calculating the growth and then in the visualisation showing the % growth against the quarter as written in the Qlabel column (Q1 2022)?
Here is the visualisation currently. the row marked as "4" is the growth from Q3 2022 to Q4 2022.
Solved! Go to Solution.
Set up a proper date table, marked as a date table, and you can use
Growth Rate from Average Sales =
VAR CurrentQuarterSales =
CALCULATE ( AVERAGE ( Assets[Sales] ), DATESQTD ( 'Date'[Date] ) )
VAR PreviousQuarterSales =
CALCULATE (
AVERAGE ( Assets[Sales] ),
DATEADD ( DATESQTD ( 'Date'[Date] ), -1, QUARTER )
)
RETURN
IF (
ISBLANK ( CurrentQuarterSales ) || ISBLANK ( PreviousQuarterSales ),
BLANK (),
DIVIDE ( CurrentQuarterSales - PreviousQuarterSales, PreviousQuarterSales )
)
Set up a proper date table, marked as a date table, and you can use
Growth Rate from Average Sales =
VAR CurrentQuarterSales =
CALCULATE ( AVERAGE ( Assets[Sales] ), DATESQTD ( 'Date'[Date] ) )
VAR PreviousQuarterSales =
CALCULATE (
AVERAGE ( Assets[Sales] ),
DATEADD ( DATESQTD ( 'Date'[Date] ), -1, QUARTER )
)
RETURN
IF (
ISBLANK ( CurrentQuarterSales ) || ISBLANK ( PreviousQuarterSales ),
BLANK (),
DIVIDE ( CurrentQuarterSales - PreviousQuarterSales, PreviousQuarterSales )
)
Thank you very much, that works perfectly and just as intended.
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 |
---|---|
56 | |
27 | |
23 | |
15 | |
11 |
User | Count |
---|---|
76 | |
58 | |
46 | |
17 | |
12 |