Add a calculated column in a Matrix (Visual Element) in terms of 2 other columns of that Matrix

[JaimedelaCasa]

Hi,

I'm struggling with the following issue, at first instance, it does not seem very difficult, but it's giving me headaches. In excel you can add a calculated field in a Dynamic table in terms of the columns of the Dynamic Table. This is really useful in the example I show next:

• In this data base, there are several orders for the same reference in the same day
• If you calculate the Reject rate per row, you get a certain value.
• However, if you want to analyze, per example, the rejection rate per material in a certain period of time, you cannot do it directly by calculating the average of the rejection rates, because there may be orders from 1 unit to 1000, and the rejection rate for each order is different. You need weighted average based on the total number of units of each order
• Excel Dynamic tables allows you to add a calculated field base on that same dynamic table. For this example you can calculated the sum of the TOTAL units, OK units and KO units and the simply add a calculated field: KO/TOTAL. 

In the example, it is easy to see the difference between both rejection rates for Ref 105:

I want to do exactly the same in Power BI, however, Power BI does not allow you to add a calculated column directly on the Matrix visual element. I guess that I need to create a new Consult or database based on the original database and sum all the TOTAL, OK, and NOK and then do the math. This is what I am struggling with.

Any help? Thanks!

Jaime

[Anonymous · Accepted solution]

Hi @JaimedelaCasa ,

Try below steps

• Go to the "Fields" pane in Power BI and locate the table or dataset that contains the columns you want to use for the calculation.

• Right-click on the table or dataset and select "New Measure." Alternatively, you can click on the "New Measure" button in the "Modeling" tab of the Power BI ribbon.

• In the formula bar that appears, you can define the calculation for your measure. For example, if you want to calculate the rejection rate as KO/TOTAL, you can use the following formula:

  less

1. Rejection Rate = DIVIDE(SUM('YourTable'[KO]), SUM('YourTable'[TOTAL]))

   Replace 'YourTable' with the actual name of your table or dataset, and 'KO' and 'TOTAL' with the names of the respective columns.

2. Press Enter to create the measure. It will appear in the "Fields" pane under the table or dataset.

3. Now, you can add the measure to your Matrix visual. Drag and drop the measure onto the appropriate area in the Matrix, such as the "Values" area.

4. The Matrix visual will display the calculated measure based on the columns you specified. It will calculate the rejection rate using the formula you defined.

By creating a measure in Power BI, you can perform complex calculations that involve multiple columns and display the result in visual elements like the Matrix. This allows you to achieve the desired weighted average and analyze the rejection rate per material over a certain period of time.

Note: Make sure your column data types are appropriate for the calculations you're performing. For example, the 'KO' and 'TOTAL' columns should be numeric or whole number data types.