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
nandic
Memorable Member
Memorable Member

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
Memorable Member
Memorable Member

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.

nandic
Memorable Member
Memorable Member

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

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!