Forum Discussion

ben15's avatar
ben15
New Member
5 years ago
Solved

Table/Matrix changes based on measure formula

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

 

Need Help
  • v-robertq-msft's avatar
    v-robertq-msft
    5 years ago

    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.

2 Replies

  • v-robertq-msft's avatar
    v-robertq-msft
    Community Support
    5 years ago

    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.