Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
You also need to filter data to display only selected month.
Demo example:
1) i added calculated column to Date table:
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
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 -->
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.
Hi,
Attached demo file. You can use this dax formula:
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:
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
81 | |
65 | |
62 |
User | Count |
---|---|
147 | |
116 | |
104 | |
88 | |
65 |