Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
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
User | Count |
---|---|
91 | |
73 | |
68 | |
63 | |
55 |
User | Count |
---|---|
99 | |
88 | |
73 | |
60 | |
58 |