Calculate average, flitered based on product attrbutes

[Jonwig]

Hi,

I am trying to create an average based on attrbutes of products.

I have a table with products and price categories.

I then have a table with sales of these products by store.  I am looking for a measure or column (please suggest which is best to tackle this, my guess is measure) to show the average sales by price category.

This is how the output should look.

                    Product sales   price category       average sales for price category (this is the calculated column)

Product 1      10                    High                               11

Product 2       5                      Mid                               5

Product 3      3                       Low                               2

Product 4       1                      Low                               2

Product  5      12                    High                             11

I am sure this is very easy but I am struggling to work it out.

Thanks

Jon

[v-huizhn-msft · Accepted solution]

Hi @Jonwig,

For your requirment, you can create calculated column and measure for smaller table. If you have large volumns of data, I suggest you create measure which will run quickly.

For creatting calculated column, please see the following screenshot.



For create a measure using the same formula.

Average = CALCULATE(AVERAGE(Table4[sales]),ALLEXCEPT(Table4,Table4[Price category]))

Then create a table visual to display the result.

If you have other issues, don't hesitate to let me know.

Best Regards,

Angelia

[Jonwig]

Thanks that works on the basic level.

If I was to add some extra dimentions to the data then how would we approach it.

So on top of the data we had previously we can add Manufacterer and store.

                   store  Product sales   price category       average sales for price category    Manufacterer

Product 1        X                  10                    High                               11                                                A

Product 2        X                  5                      Mid                               5                                                   A

Product 3        X                 3                       Low                               2                                                   B

Product 4        X                  1                      Low                               2                                                   A

Product  5       X                  12                    High                             11                                                   B

Product 6        Y                   6                     High                              6                                                   A

The average should be filtered by price category and webstore but ignore the product and the manufacterer.

My data set is currently around 10,000 rows with about 20 columns, so reasonabily small. I presume a column is still fine for this level of data?

Thanks for the first quick reply and look forward to the next response.

Jon