Table/Matrix changes based on measure formula

[ben15]

Dear community,

I am trying to calculate the forecast accuracy of three products, and I have observed some strange behavior that I can't explain.

The data

I have two input tables: Forecasts and Metadata. Forecasts contains the forecasts and the demands of three products (A, B, and C) for three time periods (1, 2, and 3). It also contains a column with the absolute deviation between the columns Demand and Forecast:

The second table, Metadata, contains additional information about the size of the products:

The two tables are linked by Article No:

The problem

I experience a problem when I try to show aggregated values and a calculated measure alongside the metadata in a table or matrix. 

In the first step, which still works as expected, I show the products (A, B, and C), the aggregated deviations, the sizes (from Metadata), and an error measure. The formula of the measure is Error = SUM(Forecasts[Absolute Deviation]) / SUM(Forecasts[Demand]).

But here comes the problem: Since I am more interested in an accuracy measure than an error measure, I would like to subtract the error from 1. But once I change the formula of my measure to 

Error = 1 - SUM(Forecasts[Absolute Deviation]) / SUM(Forecasts[Demand]), my table breaks and now looks like this:

I would love to learn what's going on here and how I can fix it!

Thank you for your help,

Ben

[v-robertq-msft · Accepted solution]

Hi, @ben15 

According to your pictures, I created the same data tables as yours, and I created this measure and place the columns like this, you can take a look:

Error =

var _error=DIVIDE(SUM(Forecasts[Absolute Deviation]),SUM(Forecasts[Demand]))

return 1-_error

And I guess this is what you want.

You can download my test pbix file here

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu]

@ben15 

please try use the article no column from metadata table.