% of columns - how to stop summing

[alssingh]

Hi all,

Here is my data table; i have calculated the % of rev column:

The Total REV column is the total amount at each company level where the type is 'Revenue'. So for Company  A this is 200 and for B this is 100.

When i table the data this is what i get:

I would like the third column to read as 100% total for sales, 19% (57/300) for expense group 2 and 22% (65/300) for expense group 1.

When i filter by 'Company' the % are correct:

So to summarise, the % is correct when looking at filtered Company level, but the overall is incorrect....i have tried to select the 'Don't Summarise' option for the % of rev column but this is not ideal as i would like the data summarised by Category:

Thanks for your help!

[Kishore_KVN]

Hello @alssingh , 

Firstly as you are not mentioning the split of Company in the table visual, you are not finding the correct percentages. 

Secondly your requirement will not deliver clear picture to the end users. But still as you have mentioned percentages are not calculated like that. 

1. For sales it will be 300/500 which is 60% 

2. For Event Group 1 it will be 65/400 which is 16.25%

3. For Event Group 2 it will be 57/300 which is 19%

If this is your requirement, then your column should be written like this:

% Contr = CALCULATE(SUM('Table'[Amount])/SUM('Table'[TotRev]),ALLEXCEPT('Table','Table'[Cat]))

Then drag it into the table visual where you have to use dont summarize as well. Then your output looks like this:

If this explanation helped you in achieving your requirement, please mark my post as solution so that other can reach to it quickly. Thanks. 

[amitchandak]

@alssingh , You should always create a measure

% = divide(Sum(Table[Amount]), sum(Table[Total Rev]))

[alssingh]

Unfortunately that didn't work. This is what i get when i try:

[amitchandak]

@Kishore_KVN , if you can put the Total Rev in visual I can test that. But seem fine to me

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.