Division Column Calculation For the Total of the column values

[Anonymous]

I have my data table that looks as follows:

The bold shaded are the total (Sum of the columns). The third column (% values) is C row/ B row.

However, when I replicate the same in Power BI ( Columns A, B and C- I enter the data manually and when i bring it as a matrix, I change it to SUM). By using the DIVISION Formula, I am able to get the individual percentage values (Column C), but the total is not the same as 215/2100 = 10%. It only allows me to choose between count/sum/average but does not do the actual division.  

Steps Shows as follows:

1. Enter Data Manually

1. Create a column for the Division.

As you can see, the total part of the division is not 215/2100. It chooses SUM as default and I can only choose between them. But Ideally i would like it to show 215/2100 - 10%.

Any Help? Thanks!!

[edhans · Accepted solution]

You don't want this in a calculated column. You want this in a measure that will dynamically handle totals and subtotals in grids and pivots.

Do this:

1. Right-click on the table and select New Measure.
2. Type this in:

3. My Division = 
   DIVIDE(
       SUM(Trial[C]),
       SUM(Trial[B])
   )

   It should show totals correctly now. You may need to change the measure's format in the Modeling tab.

[HotChilli · Accepted solution]

Since it's a straightforward divide, you could use a measure instead.

Measure = DIVIDE(SUM(Trial[C]), SUM(Trial[B]),0)

As a column, the total is simple addition.  As a measure, it evaluates according to context.

[edhans · Accepted solution]

You don't want this in a calculated column. You want this in a measure that will dynamically handle totals and subtotals in grids and pivots.

Do this:

1. Right-click on the table and select New Measure.
2. Type this in:

3. My Division = 
   DIVIDE(
       SUM(Trial[C]),
       SUM(Trial[B])
   )

   It should show totals correctly now. You may need to change the measure's format in the Modeling tab.