Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.
I have two measures that sum columns from two different tables. These tables cannot be connected through a relationship, but they are connected to the same date table.
SUMLaborHours = SUM(Labor[hours])
SUMDefectQty = SUM(Defects[Defect_Quantity])
I then find the Defects Per 1000 Labor Hours with this measure
DefectPerLaborHours =
DIVIDE(
[SUMDefectQty],
[SUMLaborHours],
0
) * 1000
That gives these numbers for the last 6 months
8.15 | 2024 05 |
14.40 | 2024 06 |
7.11 | 2024 07 |
30.39 | 2024 08 |
20.26 | 2024 09 |
52.90 | 2024 10 |
14.08 | 2024 11 |
So I would like to find the average of those numbers. Summing them gives 147.29 which should be an average of 24.54. How can I go about getting that sum and finding that average of the last 6 months?
Note: This is not calendar months which is why there are more than 6 months listed, but the amount of days equals 6 months.
Solved! Go to Solution.
Try adding ALLSELECTED to remove date filter context from within the visual but keeping the filter context from the filter on the graph.
Average Measure =
CALCULATE (
AVERAGEX ( VALUES ( 'Date'[YearMonth] ), [DefectPerLaborHours] ),
ALLSELECTED ( 'Date'[YearMonth] )
)
Proud to be a Super User!
Here's an example that assumes you've selected the six fiscal months in a slicer or filter.
Average Measure =
AVERAGEX ( VALUES ( 'Date'[YearMonth] ), [DefectPerLaborHours] )
Proud to be a Super User!
So I probably should have mentioned this, but I want to show that value on a line graph showing the average as a constant line. I have filter on the graph to only go back 6 months, but that measure just seems to follow the original DefectsPerLaborHour measure instead of a constant, single value.
Something like this:
Try adding ALLSELECTED to remove date filter context from within the visual but keeping the filter context from the filter on the graph.
Average Measure =
CALCULATE (
AVERAGEX ( VALUES ( 'Date'[YearMonth] ), [DefectPerLaborHours] ),
ALLSELECTED ( 'Date'[YearMonth] )
)
Proud to be a Super User!
That worked! Thanks for your help