100% Stacked Column Chart % Calculation

[irenelitw629]

Hi,

This might be a silly question but I could not figure it out . In below sales table, product A is 29.16% of total sales ( $470,993/ $1,615,318)

However, when I converted to 100% stacked column chart, the % become 27.67%. How is 27.67% calculated? I expect the chart to show 29.16% as per excel. Here is the file https://drive.google.com/file/d/1wzVEJmLtE6kuckDwIgObAIZGaoSjsPIY/view?usp=sharing

[v-stephen-msft · Accepted solution]

Hi @irenelitw629 ,

The problem comes from negative values in your data, and if they are all positive, the results are the same on both sides.

Once the negative value of L is added, the result is different.

If you want to keep the results displayed in the left table in the Stacked column chart, you can create the following measure.

Measure = var _sum=SUMX(FILTER(ALLSELECTED(data),[Sales Quarter]=MAX('data'[Sales Quarter])&&[Product]=MAX('data'[Product])),[Sales Amount])
var _all=SUMX(ALLSELECTED(data),[Sales Amount])
return DIVIDE(_sum,_all)

Then use the normal Stacked column chart and replace the original value with a measure, which displays the percentage.

Best Regards,

Stephen Tao

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

[DOLEARY85]

Hi,

it looks like because L is a negative percentage it's throwing off your total % calculation

[irenelitw629]

@DOLEARY85 , thanks - anyway to fix it?

[DOLEARY85]

Not with that particular chart as all the values add up to 100% if you use the standard stacked column chart it should give you what you need.

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍