Question 26

Explanation

Let’s create a calculated column for EBITDA margin as per the definition given in the question.

And display EBITDA margin on a card visual. Change the default summarization from SUM to AVERAGE as the sum of percentages doesn’t make sense.

Everything looks perfect. But there is a flaw in the calculation.

We need EBITDA margin (EBITDA as a percentage of its Revenue). So, the calculation should display a ratio of EBITDA to Revenue.

To put it more concretely, we need to display a ratio of the aggregation of EBITDA to the aggregation of Revenue.

But, the calculated column, first, calculates a ratio for every row. And then, we aggregate the ratio (by using the implicit function AVERAGE).

This produces incorrect value. Instead of displaying a ratio of the aggregations (of EBITDA & Revenue), we are displaying aggregation of the individual ratios.

Reference Link: https://docs.microsoft.com/en-us/learn/modules/dax-power-bi-add-measures/5-compare-calculated-columns-measures

Option A is incorrect.

Let’s create a measure for EBITDA Margin to solve the same problem.

Unlike for a calculated column, where we calculated an aggregation of ratios, the measure calculates a ratio (DIVIDE function) of the aggregation (SUM function) of EBITDA to the aggregation of Revenue.

If we use calculated columns, the ratio is first calculated and then aggregated. With a measure, first, all the values are aggregated with the SUM function. Then the ratio is calculated.

Doing so produces the correct result.

Reference Link: https://docs.microsoft.com/en-us/learn/modules/dax-power-bi-add-measures/5-compare-calculated-columns-measures

Option B is the correct answer.

Custom columns are added in the Power Query Editor with M formula queries when loading the data. On the contrary, you create calculated columns in the Report or Data view of the Power BI model during design time.

We use the same formula as the one used for the calculated column to create the custom column EBITDA margin.

However, this solution is no different from using a calculated column. It will produce an incorrect result.

Reference Link: https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-add-custom-column

Option C is incorrect.

PBIX File Link: Calculate & display EBITDA margin in a card visual