Forum Discussion
Microsoft EmployeeDivide Rows of same columns with row in same column
| desc | v1 | v2 | v3 | v4 | v5 |
| a | 10 | 22 | 32 | 43 | 23 |
| b | 22 | 32 | 64 | 66 | 43 |
| c | 21 | 43 | 43 | 34 | 54 |
| d | 32 | 32 | 43 | 54 | 56 |
| Z | 200 | 300 | 400 | 500 | 600 |
I want to divide a,b,c,d rows by z to calculate percentage
output should be
| desc | v1 | v2 | v3 | v4 | v5 |
| a | av1/zv1 | av2/zv2 | |||
| b | bv1/zv1 | bv2/zv2 | |||
| c | cv1/zv1 | cv2/zv2 | |||
| d | dv1/zv1 | dv2/zv2 |
like i have multiple columns(v6,v7.......)
Need percentage
Here is one way to do it. Just highlight first column and "Unpivot Other Columns", load that table and it will look like the below.
Then use this DAX expression in a calculated column.
Divide =
DIVIDE (
vZTable[Value],
CALCULATE (
MIN ( vZTable[Value] ),
ALLEXCEPT ( vZTable, vZTable[vNumber] ),
vZTable[desc] = "Z"
)
)For the measure version, just wrap the first Divide expression with SELECTEDVALUE() and use it in a table visual with the desc and vNumber columns.
If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
3 Replies
- mahoneypat
Here is one way to do it. Just highlight first column and "Unpivot Other Columns", load that table and it will look like the below.
Then use this DAX expression in a calculated column.
Divide =
DIVIDE (
vZTable[Value],
CALCULATE (
MIN ( vZTable[Value] ),
ALLEXCEPT ( vZTable, vZTable[vNumber] ),
vZTable[desc] = "Z"
)
)For the measure version, just wrap the first Divide expression with SELECTEDVALUE() and use it in a table visual with the desc and vNumber columns.
If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
vijaykumarj19Last row is not grand total it's just a row
