Create a new calculated column in matrix

[Anonymous]

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.

[v-yuta-msft]

@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_month

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

[Anonymous]

@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 · Accepted solution]

@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_month

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

[Anonymous]

Send me your pbix to fix it...(I need to see how your tables are)