Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

3 Year Average Per Franchise

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)

 

UniqueIDFranchiseDate
1221A01/19/2018
1222BB02/19/2019
1223AC06/18/2019
1224A01/19/2019
1225A01/29/2019
1226BB01/08/2019
1227BB04/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

1 ACCEPTED 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

View solution in original post

5 REPLIES 5
v-lili6-msft
Community Support
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
amitchandak
Super User
Super User

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

 

Anonymous
Not applicable

@amitchandak 

 

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 averagecount 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

Anonymous
Not applicable

Thank you so much! I think I am starting to understand the summarize/calculate table with your help.

 

I really appreciate it.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.