Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 mont...
Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    7 years ago

    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.

v-yuta-msft's avatar
v-yuta-msft
Icon for Community Support rankCommunity Support
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      v-yuta-msft
      Icon for Community Support rankCommunity Support
      7 years ago

      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.