Hi @azeenk ,
This is a classic and interesting DAX problem. The behavior you're observing, where the total is incorrect only in the unfiltered "grand total" context, points to a subtle issue with how filter context is being handled. Your current SUMX(VALUES(...)) pattern is designed to iterate through each cycletime_nm, calculate its average, and then sum those averages. While this logic appears sound, it can become unreliable when Power BI calculates the chart's total data label. In that specific context, which lacks a filter from the chart's legend, the context transition within SUMX can sometimes interact with the data model in unexpected ways, leading to the inflated values you've described.
To resolve this, we can use a more robust DAX pattern employing the SUMMARIZE function. This approach is generally more stable because it first creates an explicit virtual summary table of the averages for each group before summing the results, making it less susceptible to these context transition issues. By replacing your current measures with this pattern, you will get accurate totals in all scenarios, including the default view.
Here is the revised DAX for your sm_DaysCalc measure. It first generates a table of each cycle time and its average duration, then sums those averages.
sm_DaysCalc =
VAR SummaryTable =
SUMMARIZE(
CycleTime_IHA_POM_Sliver,
CycleTime_IHA_POM_Sliver[cycletime_nm],
"__AverageDuration", AVERAGE(CycleTime_IHA_POM_Sliver[avgdurationdays])
)
RETURN
SUMX(
SummaryTable,
[__AverageDuration]
)
You should apply the same robust pattern for the hours calculation.
sm_HoursCalc =
VAR SummaryTable =
SUMMARIZE(
CycleTime_IHA_POM_Sliver,
CycleTime_IHA_POM_Sliver[cycletime_nm],
"__AverageDurationHrs", AVERAGE(CycleTime_IHA_POM_Sliver[AvgDurationHrs])
)
RETURN
SUMX(
SummaryTable,
[__AverageDurationHrs]
)
And finally, update the minutes calculation with the SUMMARIZE logic as well.
sm_MinutesCalc =
VAR SummaryTable =
SUMMARIZE(
CycleTime_IHA_POM_Sliver,
CycleTime_IHA_POM_Sliver[cycletime_nm],
"__AverageDurationMins", AVERAGE(CycleTime_IHA_POM_Sliver[cycletime_duration_mi])
)
RETURN
SUMX(
SummaryTable,
[__AverageDurationMins]
)
Regarding your goal to have the Y-axis show an "Average" while the label shows a "Sum," it's important to clarify how the stacked column chart functions. The total height of a stacked bar is the direct sum of its individual segments, and the total data label simply displays this total value. It's not possible to have the bar's total height represent one metric and the label show a different one. The revised DAX measures above correctly set the value for each segment to its specific average.
Consequently, the total bar height and the total data label will both accurately reflect the sum of those averages, which aligns with your core requirement.
Although the DAX above fixes the calculation, summing averages can sometimes be a less meaningful metric than a true overall average. You might consider an alternative visual approach for greater clarity. One option is to use Small Multiples, where you place the cycletime_nm field in the "Small multiples" well of a standard column chart. This creates a separate chart for each cycle time, allowing for direct comparison of their individual averages without needing to sum them. For this, you would use a simple average measure instead of a "sum of averages" measure.
Another powerful alternative is the "Line and stacked column chart." You can use your revised SUMMARIZE-based measure for the column values to show the stacked "sum of averages," and then add a second, simpler measure to the line value to plot the true overall average across all selected cycle times. This provides a rich view, showing both the composition of the parts and the true average of the whole on a single visual. The measure for the line would simply be:
Overall Average Days = AVERAGE(CycleTime_IHA_POM_Sliver[avgdurationdays])
Best regards,
