How to calculate percentage of total for categories

[rauldip22]

Hi to everybody!

I have this question:

I have this data:

Month	Category	Value
April	A	45
April	B	56
April	B	33
March	A	44
March	C	65
March	C	44
April	A	64
March	B	33
April	C	67

And I would like to calculate the percentage of the value of each category into each month.

When I create the the column chart in PBI the result is:

And when I select "Show value as percent of grand total", PBI calculates the percetaje of total of all VALUES, not only of each month. The result is:

When my idea is that in green appears the percentage of A, B and C ONLY FOR THE APRILS TOTALS, and in black the percetaje of A, B and C ONLY FOR THE MARCH TOTALS.

Could you help me?

[DevadathanK]

@v-danhe-msft Thank you for demonstrating!
In a similar manner, can we find the total percentage Cateory wise?

Like the 2 bars in category 'A' should add up to get 100% and so on. Each category needs to add upto 100%.

[v-danhe-msft]

Hi @rauldip22,

could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[v-danhe-msft · Accepted solution]

Hi @rauldip22,

Based on my test, you can refer to below steps:

1.I have entered some sample data to test for your problem like the picture below:

2.Create a measure.

MeasureA = (CALCULATE(SUM(Table1[Value]),FILTER('Table1','Table1'[Category]=MAX('Table1'[Category])&&'Table1'[Month]=MAX('Table1'[Month]))))/CALCULATE(SUM(Table1[Value]),FILTER(ALL('Table1'),'Table1'[Month]=MAX('Table1'[Month])))

3.Create a Clustered column chart and add the related fields. Now you can see the result.

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/ktmqry6tfgdz0z4/How%20to%20calculate%20percentage%20of%20total%20for%20cat...

Regards,

Daniel He

[FilipaPereira]

Hi,

I have this set of data and I want to calculate a column with the total amount per category, the same as the total amount column, but now by category. That is, the total value appears but by category in all rows of the category.
 This value must remain fixed, even when filtering by product or order number.
I'm not able to do this calculation, can you help?

[Anonymous]

In the same example, if we have only two columns i.e. Month and Categories and we want to find out the percentage of repsonses in 2nd column. How we would do that? For example - What % of A's, B's & C's are there in total and every month. This is to be shown in Stacked in column chart. Thanks in Advance.