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

[rleseberg]

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.

Client	Revenue	Diff from Average
X	6000	-2666.67
Y	8000	-666.67
Z	12000	3333.33
	8666.67

Any help would be appreciated,

thank you.

[Anonymous]

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.

[rleseberg]

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]

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:

Regards,
Nemanja Andic

[rleseberg]

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 · 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:

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