Calculating measure

[aktripathi2506]

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

[GilbertQ]

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.

[parry2k]

hi @GilbertQ 

I agreed with your approach somehwat but sometime it is not possible that you can create the data at source level, so it depends on the situation.

[GilbertQ]

Hi @parry2k

That is true, but you can always edit the data in the Query Editor before you bring it into your Power BI Model.

In doing it in this way, your data at source is not modified, but rather you make the changes in the Query Editor which is part of Power BI Desktop.

[parry2k]

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