Forum Discussion
Calculating measure
Hi,
| Week | Item | Count of Item | avg sale for that item |
| 1 | 121 | 46 | 133 |
| 1 | 122 | 1 | 57 |
| 1 | 123 | 6 | 54 |
| 1 | 124 | 12 | 96 |
| 1 | 125 | 26 | 66 |
| 1 | 126 | 11 | 114 |
| 1 | 127 | 53 | 135 |
| 1 | 128 | 5 | 75 |
| 1 | 129 | 5 | 110 |
| 1 | 130 | 13 | 101 |
Raw data is something like this:
item 121 came 46 times and each time and its average sale price among 46 records was 133
item 122 came 1 time and its avg sale price was 57
item 123 came 6 times in the raw data and it was having avg sale 54 for all 6 records
and so on
There are several weeks too, I will be using week as a slicer.
Now what I am looking for is - to write a measure which can add the average of these items
for example 941 in this case
so when I select week 1 from slicer , I get the total item counts and sum of there avg.
my final desired output is: 178/941
This is what I am looking for.
I will be thankful to any help in this direction.
Thanks
4 Replies
Add new column in your table and o get sum of the average, something like this:
my average column = var myItem = myTable[Item] var myCount = CALCULATE(COUNT(myTable[Item]), FILTER(ALL(myTable),myTable[Item] = myItem)) var mySum = CALCULATE(SUM(myTable[Sale]), FILTER(ALL(myTable),myTable[Item] = myItem)) return DIVIDE(mySum,myCount,0)
try this and see if this works. We would have done it in few lines but try to be more detailed.
we can add another column for count as well.
Thanks,
P
Hi aktripathi2506,
I would suggest if possible to always look at creating measures before storing the data in each column. Reason being is that if you want to the slice your data with another field, or possibly just get the average for each week, and remove the items, using the column method will mean that you have to create a new column. Which takes time, is not efficient and can cause performance problems in certain instances.
I solved your issue by creating the following measures:
Counts = SUM('Table1'[Count of Item]) Avgs = SUM('Table1'[avg sale for that item]) Output = DIVIDE([Counts],[Avgs]) - Formatted as %And this was the output, as you can see for the Table total it is the expected 18.92%, as well as you can have other visuals.
