Forum Discussion
Solved
Problem with getting totals
I am sure there is a very easy answer to this!
Basically i have a table like this (simplified for here)
| Proj | L/M | DD | Date | Coefficient |
| A | M | Build | 01/05/2028 | 0 |
| A | M | Build | 01/06/2028 | 0.00784261 |
| A | M | Build | 01/07/2028 | 0.0680792 |
| A | M | Build | 01/08/2028 | 0.23133047 |
| A | M | Build | 01/09/2028 | 0.50448799 |
| A | M | Build | 01/10/2028 | 0.71814098 |
| A | M | Build | 01/11/2028 | 0.91698592 |
| A | M | Build | 01/12/2028 | 1.04398334 |
| A | M | Build | 01/01/2029 | 1.07310363 |
| A | M | Build | 01/02/2029 | 0.91556296 |
| A | M | Build | 01/03/2029 | 0.51749098 |
| A | M | Build | 01/04/2029 | 0.00299192 |
| A | M | Build | 01/05/2029 | 0 |
| A | M | Build | 01/06/2029 | 0 |
| A | M | Build | 01/07/2029 | 0 |
| B | M | Build | 01/05/2028 | 0 |
| B | M | Build | 01/06/2028 | 0.0039213 |
| B | M | Build | 01/07/2028 | 0.0340396 |
| B | M | Build | 01/08/2028 | 0.11566524 |
| B | M | Build | 01/09/2028 | 0.25224399 |
| B | M | Build | 01/10/2028 | 0.35907049 |
| B | M | Build | 01/11/2028 | 0.45849296 |
| B | M | Build | 01/12/2028 | 0.52199167 |
| B | M | Build | 01/01/2029 | 0.53655181 |
| B | M | Build | 01/02/2029 | 0.45778148 |
| B | M | Build | 01/03/2029 | 0.25874549 |
| B | M | Build | 01/04/2029 | 0.00149596 |
| B | M | Build | 01/05/2029 | 0 |
| B | M | Build | 01/06/2029 | 0 |
| B | M | Build | 01/07/2029 | 0 |
Basically what i need to do is normalise the data in the [Coefficient] column based on the columns [Proj], [L/M] and [DD]
So basically i am after a new column, with the value of [Coefficient] divided by the total of all the [Coefficients] for that specific grouping of [Proj], [L/M] and [DD]
So the total of all the [Coefficients] for [A], [M] and [Build] is 6
So for each line i need for example
0.00784261] / 6 = 0.0013071
0.0680792 / 6 =0.011346
etc
Any help welcomed
TimK,
Try this calculated column:
New Column = VAR vNumerator = Table1[Coefficient] VAR vDenominator = CALCULATE ( SUM ( Table1[Coefficient] ), ALLEXCEPT ( Table1, Table1[Proj], Table1[L/M], Table1[DD] ) ) VAR vResult = DIVIDE ( vNumerator, vDenominator ) RETURN vResult
1 Reply
TimK,
Try this calculated column:
New Column = VAR vNumerator = Table1[Coefficient] VAR vDenominator = CALCULATE ( SUM ( Table1[Coefficient] ), ALLEXCEPT ( Table1, Table1[Proj], Table1[L/M], Table1[DD] ) ) VAR vResult = DIVIDE ( vNumerator, vDenominator ) RETURN vResult