cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rleseberg
Frequent Visitor

Creating a measure to find an average, ignoring a column, filtered by a date slicer

Hello Community,

 

I have a visual that displays clients and their respective revenue.

A slicer controls which month I am analyzing (from a related calendar table, not the date in my dataset).

 

I am trying to calculate the difference between a single client's revenue and the average client revenue, during the same time frame (see the third column below).. I've messed around with ALL, ALLEXCEPT, and AVERAGEX, but can't find a solution or a post that can point me in the right direction.

 

 

ClientRevenueDiff from Average
X6000-2666.67
Y8000-666.67
Z120003333.33
 8666.67 

 

 

Any help would be appreciated,

thank you.

1 ACCEPTED SOLUTION

You also need to filter data to display only selected month.
Demo example:
1) i added calculated column to Date table:

Month = FORMAT('Date'[Date],"mmmm") (returns January, February)
2) i added measure to check if date from main table is the same as slicer value (which uses value from Date table)
SameMonth = IF(FORMAT(MIN('Table'[Date]),"mmmm") = SELECTEDVALUE('Date'[Month]),1,0)
 
Now add this measure to filter (page filter, visualization filter, whichever needed) and select only if value 1 and apply that filter.


Result:

 

avg per client v1.PNG

avg per client v2.PNG

 

As you can see second column in list (Date column), on first image it displays only data for January, on second image it displays only data for February (according to selection).

Regards,
Nemanja Andic

View solution in original post

5 REPLIES 5
gauravtanwar
Resolver II
Resolver II

I tried finding the solution through calculated measures but it seems we need to create calculated columns to sove this one. Below are the steps I performed:

1. Create a column like this --> 

RevenueAverage = SUM(Data[Revenue])/COUNT(Data[Client])
2. Now create a second column like this --> 
Revenue_difference = Data[Revenue]-Data[RevenueAverage]
You should get the required result.

Thank you, but I should have been more clear. The above is my visual. It is taking the average of the line items in my dataset. Let's say the rows are "sales", and column B above is the average of those.

nandic
Super User
Super User

Hi, 

Attached demo file. You can use this dax formula:

Avg per Client = CALCULATE(AVERAGE('Table'[Revenue]),ALLEXCEPT('Table','Table'[Client])) 

This is regular average measu
Average Revenue = AVERAGE('Table'[Revenue])
 
And this is the difference:
Avf Diff = [Average Revenue]-[Avg per Client]
 
Result:
avg per client.PNG
 
Regards,
Nemanja Andic
 
rleseberg
Frequent Visitor

I may have not included enough detail in my file. The underlying data is at the "sale" level, and the revenue I displayed is the average per client (in a visual). 

When I tried you calculation, I also had issues with the date being all-time, and not in the specified month in a slicer.

You also need to filter data to display only selected month.
Demo example:
1) i added calculated column to Date table:

Month = FORMAT('Date'[Date],"mmmm") (returns January, February)
2) i added measure to check if date from main table is the same as slicer value (which uses value from Date table)
SameMonth = IF(FORMAT(MIN('Table'[Date]),"mmmm") = SELECTEDVALUE('Date'[Month]),1,0)
 
Now add this measure to filter (page filter, visualization filter, whichever needed) and select only if value 1 and apply that filter.


Result:

 

avg per client v1.PNG

avg per client v2.PNG

 

As you can see second column in list (Date column), on first image it displays only data for January, on second image it displays only data for February (according to selection).

Regards,
Nemanja Andic

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!