Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello PowerBI Community,
I am struggling to figure this query out and I think it should be relatively straight forward:
I have Franchise Transaction Data in the following (UniqueID responds to transactionID)
UniqueID | Franchise | Date |
1221 | A | 01/19/2018 |
1222 | BB | 02/19/2019 |
1223 | AC | 06/18/2019 |
1224 | A | 01/19/2019 |
1225 | A | 01/29/2019 |
1226 | BB | 01/08/2019 |
1227 | BB | 04/19/2019 |
I want to create a 3-Year and 5-Year average measure for each franchise to have a better baseline to look at historical franchise performance.
I've tried various measures on here to do that but the issue I am struggline with is that I have to COUNT the number of filter by date and franchise, then count the number of TransactionIDs to get total number of Transactions in that Fiscal Year. In ordrer to get the average wouldn't I then need to average that COUNT over the fiscal years. I'm unsure how to "average a count" in PowerBI.
Does this make sense?
I can provide PBIX file if needed.
Thanks for any hel,
Michael
Solved! Go to Solution.
Edited
Try this
Avg 3 = AVERAGEX(SUMMARIZE(CALCULATETABLE(Sheet1,DATESINPERIOD('Date'[Date],max(Sheet1[Date]),-3,YEAR)),'Date'[Year],"_cnt",COUNT(Sheet1[UniqueID])),[_cnt])
Link :https://www.dropbox.com/s/rwev9wcbt1jibha/Franchise.pbix?dl=0
hi @Anonymous
You may refer to this similar blog:
https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/
If you still have problem, please share your sample pbix file and your expected output.
You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.
Regards,
Lin
Try following with the date calendar in place and joined with your date
First will give three-years count. Second try to group the count by year and take Avg
Rolling 3 = CALCULATE(count(table[UniqueID]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Tble[Date]),-3,year))
averagex(
summarize(filter(table,DATESINPERIOD('Date'[Date],ENDOFMONTH(Tble[Date]),-3,year)),date[year],"_cnt",count(table[UniqueID])),[_cnt])
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/
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin
Thank you for your help on this. I truly appreciate it. As someone who natively codes in Python (I try to avoid using it in PowerBI as much as possible) using DAX can be a little challenging at times.
I tried your solution and the Rolling 3 measure gives the expected result for the rolling 3 year sum. However, the second measure keeps throwing me an error : " A table of multiple values was supplied where a single value was expected". I believe I have translated the code that you provided correctly to match my date table.
As an aside, I am actually only interested (at this point in time) in the most recent 3 year average for each franchise. I only want the average from the last three fiscal years. I have created a custom column on my date table that has Values of True (if the date is in the last three fiscal years) and a value of FALSE ( if the date is not in the last three fiscal years). Perhaps this is not the best way to do it, but it makes sense to me.
For finding the three year average for franchise performance score (an in house metric) I use the following formula:
3YearPerformanceAverage = CALCULATE(AVERAGE('PerformanceScores'[Score]), dateDim[3Years] = TRUE() , 'PerformanceScores'[Score] <> BLANK())
This works because there is only one score per franchise per year. So all I need to do is average the filtered table that is returned. However, as the OP states, when I need to average a count of transactions per each franchise - that is where I am having issues.
I hope this helps provide more context and show that I am only interested in the most immediate 3 Year Average of the Count (total) transactions per franchise.
Thanks!
M
Edited
Try this
Avg 3 = AVERAGEX(SUMMARIZE(CALCULATETABLE(Sheet1,DATESINPERIOD('Date'[Date],max(Sheet1[Date]),-3,YEAR)),'Date'[Year],"_cnt",COUNT(Sheet1[UniqueID])),[_cnt])
Link :https://www.dropbox.com/s/rwev9wcbt1jibha/Franchise.pbix?dl=0
Thank you so much! I think I am starting to understand the summarize/calculate table with your help.
I really appreciate it.
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |