cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Akshaya Member
Member

Division Column Calculation For the Total of the column values

I have my data table that looks as follows:

Education SC.PNG

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.PNG

  1. Create a column for the Division.

2.PNG

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%.3.PNG

Any Help? Thanks!!

2 ACCEPTED SOLUTIONS

Accepted Solutions
edhans New Contributor
New Contributor

Re: Division Column Calculation For the Total of the column values

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 Established Member
Established Member

Re: Division Column Calculation For the Total of the column values

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.

2 REPLIES 2
edhans New Contributor
New Contributor

Re: Division Column Calculation For the Total of the column values

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 Established Member
Established Member

Re: Division Column Calculation For the Total of the column values

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.