Forum Discussion
Divide each row by categories in a table
Dear All,
I would like to have a measure column that divide rows of sales with channel X and Y for each date. How can I do that please help?
Date Channel Sales
12/01/20 X 20
12/01/20 Y 10
03/05/20 X 200
03/05/20 Y 50
The desired outcome with desired measure ratio column will be below
Date Ratio
12/01/20 20/10=2
03/05/20 200/50=4
Thank you
Please try a measure expression like this
XY Ratio =
VAR vX =
CALCULATE (
SUM ( Table[Sales] ),
Table[Category] = "X"
)
VAR vY =
CALCULATE (
SUM ( Table[Sales] ),
Table[Category] = "Y"
)
RETURN
DIVIDE (
vX,
vY
)Pat
5 Replies
- mahoneypat
Microsoft Employee
Please try a measure expression like this
XY Ratio =
VAR vX =
CALCULATE (
SUM ( Table[Sales] ),
Table[Category] = "X"
)
VAR vY =
CALCULATE (
SUM ( Table[Sales] ),
Table[Category] = "Y"
)
RETURN
DIVIDE (
vX,
vY
)Pat
- Ashish_Mathur
Super User
- Carmichael
Advocate III
Reshape the data in power query so you have one row per date and a column for x then y.
Did I answer your question? Mark my post as a solution if I did! Like it if it helped. Appreciate the Kudos!!load into power bi and create measure to
Divide measure:Var _x = sum(tableName[x])
Var _y = sum(tableNmae[y]
RETURNDivide(_x, _y)
Actually there is only one table. What Var _x = sum(tableName[x]) stands for? I don't understand the solution.
- Carmichael
1. Go to PQ
2. Select Channel column
3. Pivot channel into columns
https://radacad.com/pivot-and-unpivot-with-power-bi
your values column = salesthat should give you a reshaped table with one column x values and one y making it much easier to do the ratio calculation.
you have the choice of calculating the ratio by creating a new custom colum in PQ or you can create in DAX
var stands for variables which are highly recommended to learn. More herehttps://docs.microsoft.com/en-us/power-bi/guidance/dax-variables
