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.
having trouble figuring out if I can get an average of a QOQ% MEasure I created? the first table shows how my data is set up.
my QOQ % calculations are working fine
Qtr | Item | waste (is a measure) | QOQ% (also dynamic measure) |
1 | 1234 | 10 | |
1 | 567 | 12 | |
1 | 8910 | 19 | |
1 | 1121314 | 20 | |
2 | 1234 | 11 | 10% |
2 | 567 | 12 | 0% |
2 | 8910 | 23 | 21% |
2 | 1121314 | 9 | -55% |
3 | 1234 | 2 | -82% |
3 | 567 | 5 | -58% |
3 | 8910 | 64 | 178% |
3 | 1121314 | 24 | 167% |
4 | 1234 | 32 | 1500% |
4 | 567 | 53 | 960% |
4 | 8910 | 64 | 0% |
4 | 1121314 | 64 | 167% |
I want to achieve the table below with Average % change of the QOQ%, i also dont want any filter apply to the dates so its just a running change as we keep adding years or additional quarters.
Item | Average change (no filters should apply always) | |
1234 | 476% | average change between all quarters (so adding 3 quarters QOQ% divide by 4) |
567 | 300% | |
8910 | 66.30% | |
1121314 | 93% |
-----------------------
FYI The original request was to find the trend but didnt think this was easy in PBI so instead just calculating the "average change" instead of linest function from excel
Solved! Go to Solution.
Hey @powerbihelp87 ,
I guess this measure provides what you are looking for:
Measure =
AVERAGEX(
ADDCOLUMNS(
CROSSJOIN(
VALUES('Table'[Item])
, ALL('Table'[Qtr])
)
, "_measure " , [QOQ% (also dynamic measure)]
)
, [_measure ]
)
It allows to create these table visuals:
Hopefully, this provides what you are looking for.
Regards,
Tom
stay safe, stay healthy, and Power On
Hey @powerbihelp87 ,
I guess this measure provides what you are looking for:
Measure =
AVERAGEX(
ADDCOLUMNS(
CROSSJOIN(
VALUES('Table'[Item])
, ALL('Table'[Qtr])
)
, "_measure " , [QOQ% (also dynamic measure)]
)
, [_measure ]
)
It allows to create these table visuals:
Hopefully, this provides what you are looking for.
Regards,
Tom
stay safe, stay healthy, and Power On
You can get last qtr by using last qtr no like
Last Qtr waste = calculate([waste ], filter(Table,Table[Qtr] <=max(Table[Qtr])-1))
but better if you have qtr year column like this(YYYYQQ) and create a rank
Rank = Rankx(all(Table),Table[Qtr])
Last Qtr waste = calculate([waste ], filter(Table,Table[Rank] <=max(Table[Rank])-1))
My advice would be to Qtr to a different table as all will remove other filters
Refer how to use Rank for prior - https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0
If you have date, then use date calendar and Time intelligence
QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Last complete QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER(dateadd('Date'[Date],-1,Year))))
trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER))
trailing 4 QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-4,QUARTER))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
just noticed the second half didnt populate when I first viewed it! I will try and see how it works out thank you!
@amitchandak Thank you for looking into my issue!
I am able to get the QOQ% but want the average for all quarters, not sure if that is possible.
so lets say item 1234
q1- 10% QOQ
q2-23% QOQ
q3-10% QOQ
calculation is (10+23+10)/3
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 |
---|---|
110 | |
94 | |
80 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |