The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Would it be possible to create a caluclated column that could find the average of each salesmans sales so that looks a little like this example?
Day | Salesman | Sales | Average |
Monday | John | 200 | 277.5 |
Monday | Steve | 350 | 625 |
Monday | Billy | 100 | 170 |
Tuesday | John | 355 | 277.5 |
Tuesday | Steve | 900 | 625 |
Tuesday | Billy | 240 | 170 |
I know I could format the a total to show the average in a visual and I have a measure that does the same thing, but I was wondering if I could do this in a calulated column?
Solved! Go to Solution.
Please try this column expression. Replace T1 with your actual table name.
AvgSales =
CALCULATE (
AVERAGE ( T1[Sales] ),
ALLEXCEPT ( T1, T1[Salesman] ),
T1[Sales] > 0
)
Pat
Please try this column expression. Replace T1 with your actual table name.
AvgSales =
CALCULATE (
AVERAGE ( T1[Sales] ),
ALLEXCEPT ( T1, T1[Salesman] ),
T1[Sales] > 0
)
Pat
Outstanding, that's cracked it! You two have been brilliant help!
Try this
Avg = CALCULATE(AVERAGEX('DataTable','DataTable'[Sales]), FILTER('DataTable', 'DataTable'[Salesman] = EARLIER('DataTable'[Salesman])))
Regards
Phil
Proud to be a Super User!
Oh that is fantastic! Would you know how I could exclude any values that were 0 and / or blanks?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
141 | |
109 | |
108 | |
76 | |
63 |
User | Count |
---|---|
273 | |
129 | |
123 | |
101 | |
91 |