Forum Discussion
Create a new calculated column in matrix
Hi guys,
I'm quite new with Power Bi and i am using a matrix to group my data like this.
Now I want to add a new column which allow me to make a new calculation = ([q'ty per unit of 3rd month] - [q'ty per unit of 2nd month]) * [Price per Unit of 3rd month]
Is there any simple way help me to do so since I'm not an expect with DAX. Thanks for your help.
Anonymous ,
You can modify the measure above as below:
Result =
VAR qty_per_unit_of_first_month_in_slicer =
CALCULATE ( MIN ( Table[q'ty per unit] ), ALLSELECTED ( Table ) )
VAR qty_per_unit_of_last_month_in_slicer =
CALCULATE ( MAX ( Table[q'ty per unit] ), ALLSELECTED ( Table ) )
VAR Price_per_Unit_of_last_month =
CALCULATE (
MAX ( Table[Price per Unit] ),
FILTER ( Table, Table[Month] = qty_per_unit_of_last_month_in_slicer )
)
RETURN
qty_per_unit_of_last_month_in_slicer - qty_per_unit_of_first_month_in_slicer * Price_per_Unit_of_last_monthCommunity Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
v-yuta-msftCommunity Support
Anonymous ,
Generally, you can create a measure using DAX like pattern below and drag the measure to the value field:
Result = VAR qty_per_unit_of_3rd_month = CALCULATE ( MAX ( Table[q'ty per unit] ), FILTER ( Table, Table[Month] = 3 ) ) VAR qty_per_unit_of_2rd_month = CALCULATE ( MAX ( Table[q'ty per unit] ), FILTER ( Table, Table[Month] = 2 ) ) VAR Price_per_Unit_of_3rd_month = CALCULATE ( MAX ( Table[Price per Unit] ), FILTER ( Table, Table[Month] = 3 ) ) RETURN qty_per_unit_of_3rd_month - qty_per_unit_of_2rd_month * Price_per_Unit_of_3rd_monthCommunity Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-yuta-msfthi,
It worked for me, in this case i used the slicer to select month 2 to 3, and the DAX you provided also apply to month 2 & 3.
How can i change the DAX so that it will be apply directly to the slicer, for example: I selected to compare the month 1 &4 and the Dax Measure also changed to month 1 & 4?
- v-yuta-msft
Anonymous ,
You can modify the measure above as below:
Result =
VAR qty_per_unit_of_first_month_in_slicer =
CALCULATE ( MIN ( Table[q'ty per unit] ), ALLSELECTED ( Table ) )
VAR qty_per_unit_of_last_month_in_slicer =
CALCULATE ( MAX ( Table[q'ty per unit] ), ALLSELECTED ( Table ) )
VAR Price_per_Unit_of_last_month =
CALCULATE (
MAX ( Table[Price per Unit] ),
FILTER ( Table, Table[Month] = qty_per_unit_of_last_month_in_slicer )
)
RETURN
qty_per_unit_of_last_month_in_slicer - qty_per_unit_of_first_month_in_slicer * Price_per_Unit_of_last_monthCommunity Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.