Forum Discussion
Advocate IIShowing incorrect avg sum ?
Hii
I am facing a critical issue in Power BI related to the Stacked Column Chart total data labels. I have a Stacked Column Chart where I want to display the sum of average durations of CycleTime (cycletime_nm) on top of the bar. The bar height should reflect the average, but the label on top of the bar should show the sum of selected cycle times. The issue is that: When I select a single cycletime_nm, the total label shows correct values. When I multi-select a few cycle times, it sums correctly and displays the accurate total label. But when no filter is applied (i.e., default view, all cycle times selected), the bar total label shows incorrect/inflated values.
Hi malhotra6675 ,
Thank you for reaching out to the Microsoft Community Forum.
Hi rohit1991 , Thank you for your prompt response.
Hi malhotra6675 ,
The bar height is based on AVERAGE(), but the total label in stacked visuals is the SUM() of segment values, which is bot equal to logical "sum of averages". You need to divide the logic for the bar height and the total label.
Measure for Bar height:
AvgCycleTime =
AVERAGEX(
VALUES(Table[CycleTime_nm]),
AVERAGE(Table[Duration])
)Measure for Total Label:
SumOfAvgCycleTime=
SUMX (
VALUES ( Table[CycleTime_nm] ),
CALCULATE ( AVERAGE(Table[Duration]) )
)In stacked column chart, drag AvgCycleTime and place SumOfAvgCycleTime in data label.
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
3 Replies
v-dineshyaCommunity Support
Hi malhotra6675 ,
Thank you for reaching out to the Microsoft Community Forum.
Hi rohit1991 , Thank you for your prompt response.
Hi malhotra6675 ,
The bar height is based on AVERAGE(), but the total label in stacked visuals is the SUM() of segment values, which is bot equal to logical "sum of averages". You need to divide the logic for the bar height and the total label.
Measure for Bar height:
AvgCycleTime =
AVERAGEX(
VALUES(Table[CycleTime_nm]),
AVERAGE(Table[Duration])
)Measure for Total Label:
SumOfAvgCycleTime=
SUMX (
VALUES ( Table[CycleTime_nm] ),
CALCULATE ( AVERAGE(Table[Duration]) )
)In stacked column chart, drag AvgCycleTime and place SumOfAvgCycleTime in data label.
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
rohit1991Super User
Hi malhotra6675
This is a limitation of stacked column charts in Power BI. The total label on top is just the sum of the bar segments, so when the bar itself is showing an average, the total becomes inflated if all categories are visible.
To fix this, you need a separate measure for the total instead of relying on the built-in label. For example:
Avg CycleTime = AVERAGE ( Fact[CycleTime] )Total Sum of Avg = SUMX ( VALUES ( Fact[cycletime_nm] ), CALCULATE ( [Avg CycleTime] ) )-
Use [Avg CycleTime] for the bar height.
-
Add [Total Sum of Avg] as a line in a combo chart and show its data label on top.
-
This way, the bar still shows the average, but the label always shows the correct sum of selected cycle times, even when no filter is applied.
-
- FBergamaschi
For the data labels, use this measure showing the lables of it (you might need to add this measure to the chart)
Total Sum of Avg = SUMX ( VALUES ( Fact[cycletime_nm] ), [Your Measure For Average] )If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
