Need Help, I want to show Avg total by enabling TOTALS of STACKED Column Chart in Power BI

[Sky20997]

Hello Everyone, 

I would like to show Avg Sales in the Totals part but when I am enabling the TOTAL labels in the Stacked Column Chart, it is simply Summing up the Avg Sales, but my requirement is to show Avg of Avg [Sales]. What is the way around, I have tried using DAX INSCOPE() but that is working for MATRIX Total not in Column Chart Totals. 

I am using Year wise Avg Sales stacked by Region. 

Kindly help please. Need your suggestion on how to show those total at the avg sales at top of the bars.

[Ashish_Mathur]

Hi,

Share the download link of the PBi file.  Show the problem and the expected result clearly.

[Sky20997]

@Ashish_Mathur  Sorry for the lack of information, but I maynot be able to share the pbix file as a whole, I have tried to explain my requirement in the image below. 

Can you please suggest the best scenario for this ? 

[Anonymous]

Hi, @Sky20997 
Maybe you can use combined charts (line and stacked column charts) are implemented. I create a sample table and measures(Avg and TotalAvg).

AvgSales = AVERAGE('Table'[SalesAmount])

TotalAvgSales = CALCULATE(AVERAGE('Table'[SalesAmount]), ALL('Table'[Region]))

Then put Avg measure in column y-axis and TotalAvg in line y-axis. Here is my preview:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sky20997]

Thank you @Anonymous  for the prompt reply, this solution may not be ideal for my scenario. As I am using aCombo Chart in the first place.  I am showing YOY% trend in the line y-axis, it is the TOTALS were I would like to show something like this 

What do you suggest, how can I achieve it ? 

[Anonymous · Accepted solution]

Hi, @Sky20997 

You just use the Line and stacked column chart. 

Then create measures:

AvgSales = AVERAGE('Table'[SalesAmount])

AvgOfAvgSales = 
AVERAGEX(
    SUMMARIZE(
        'Table',
        'Table'[Year],
        "AvgSalesPerYear", [AvgSales]
    ),
    [AvgSalesPerYear]
)

Add Year to the X-axis. Add the AvgOfAvgSales measure to the line y-axis. Add AvgSales to the column y-axis and add Region to the column legend. Here is my preview:

If you want to display total labels, you can turn them on in the format navigation bar

You can refer to the following link:

Showing the Total Value in Stacked Column Chart in Power BI - RADACAD

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.