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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
DaveyP
Frequent Visitor

Average Weekly Sales

Hi 

 

I am having trouble working out an average sales DAX expression, it is for a group of stores where the data table (Keys) contains one line per store per week,

 

The issue i am facing is all stores were not open for all weeks so the average needs to be worked out on a store level based on the weeks they were open

 

i currently have created a calculated column within a structure table that only has one itterance of each store as below

 

AWUS (structure) = calculate(sum(Keys[Royalties])/DISTINCTCOUNT(Keys[Week]),RELATEDTABLE(Keys))

 

This gives me an average sales by store but none of the time filters on the page have any effect ( i assume this is the average sales for all the data relevent to each store)

 

How can i make the filters made on the date table effect this column?

 

Thanks in advance!

1 ACCEPTED SOLUTION

GOT IT!!!

 

AWUS Actual =
VAR __CATEGORY_VALUES = VALUES('Keys'[MonDate])
RETURN
 DIVIDE(
  SUMX(
   KEEPFILTERS(__CATEGORY_VALUES),
   CALCULATE(SUM('Keys'[Royalties]) / DISTINCTCOUNT('Keys'[Store Name]))
  ),
  SUMX(
   KEEPFILTERS(__CATEGORY_VALUES),
   CALCULATE(DISTINCTCOUNT('Keys'[MonDate]))
  )
 )

View solution in original post

9 REPLIES 9
v-xjiin-msft
Solution Sage
Solution Sage

Hi @DaveyP,

 

Generally, if we want to filter data based on a Date table. We can

 

1. Create a relationship on the Date table and source table.

2. Add Filter() inside the expression.

 

Since you didn't share us your table structure or some sample data. It is hard for us to give a specific solution. Thus, please kindly share us more information which can help us understand your requirement more clearly.

 

Thanks,
Xi Jin.

Sorry let me try and be more clear, in the example below 4 stores accross 4 weeks. The average store sales for the group is not simply total sales / number of stores / number of weeks. This wont work because 2 stores were not open in week 4 so to work it out i need to work out store by store then average it eg - 

 

Store 1 total sales / 4

store 2 total sales / 4 

store 3 total sales / 3

store 4 total sales / 3 

 

Then average the 4 results

 

Question is how can i do this in DAX in a way that both the stores and weeks are still filterable on the page.

 

StoreWeekSales
Store 1week 1400
Store 2week 1500
Store 3week 1600
Store 4week 1700
Store 1week 2500
Store 2week 2600
Store 3week 2700
Store 4week 2800
Store 1week 3700
Store 2week 3800
Store 3week 3900
Store 1week 4600
Store 2week 4700
Store 3week 4800

DaveyP, 

 

I have something that may work for you as it is something similar to a project of mine.

 

With the data you supplied:

 

Create two measures:

TotalSales = SUM(Table1[Sales])
CountWeeks = DISTINCTCOUNT(Table1[Week]) 

The third measure for Avg/Week:

AvgSales/Week = DIVIDE([TotalSales],[CountWeeks],BLANK())

To get something like the following:

6.PNG

 

This should work with your dataset.

 

In my project, while I have a dCalendar table, I had to take a DISTINCTCOUNT of the 'Dates' from the working table rather than the dCalendar table so the denominator was in the 1000's range rather than10,000's.

 

 






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Hi Dozer

 

Thanks for your reply but this still doesnt give the right result, it is still total sales/total distinct weeks and doesnt work when 1 store wasnt open for that week

 

Dave

Hi @DaveyP, may I ask what you expect the answer to be in your dataset that you provided?






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Hi Dozer

 

Sorry to confuse, below is the example in more detail, the results i keep getting are essentially all stores sales added together (9300) divided by count of stores (2325) divided by count of weeks (581.25)

 

WeekStore1Store2Store3Store4AWUS 
1400500700600550(All stores sales divide by count of store (4))
2500600800700650(All stores sales divide by count of store (4))
3600700900800750(All stores sales divide by count of store (4))
4700800  750(All stores sales divide by count of store (2))
       
     675

Average sales = all weeks AWUS / Count of weeks

@DaveyP,

 

Well this is interesting...adding an Average Line, You get the $675 but I do not know how it's calculated.

 

Capture.PNG

 






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Anonymous
Not applicable

How about this solution?

1) create a measure

AWUS = AVERAGEX(VALUES(Table1[Week]),CALCULATE(AVERAGE(Table1[Sales])))

 

 2) use matrix, set AWUS as Values.

GOT IT!!!

 

AWUS Actual =
VAR __CATEGORY_VALUES = VALUES('Keys'[MonDate])
RETURN
 DIVIDE(
  SUMX(
   KEEPFILTERS(__CATEGORY_VALUES),
   CALCULATE(SUM('Keys'[Royalties]) / DISTINCTCOUNT('Keys'[Store Name]))
  ),
  SUMX(
   KEEPFILTERS(__CATEGORY_VALUES),
   CALCULATE(DISTINCTCOUNT('Keys'[MonDate]))
  )
 )

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.