Count number of times one is bigger than the other

[Anonymous]

Hello,

My data looks like this:

Store	Product	Time stamp	Weight
A	a	12-Mar-20	10
A	a	15-Mar-20	11
A	b	15-Apr-20	12
B	a	15-May-20	9
B	b	15-Apr-20	9

I would like to create a visual that shows how many times the average weight of Product a is more than the average weight of b, same as b, or less than b.

The desired output looks like this:

	# of times
average(a) > average(b)	0
average(a) = average(b)	1
average(a) < average(b)	1

I have trouble calculating the count and the average at the same time, because different products and stores can have different time stamps. How can I go about solving this issue?

Thank you very much for your time. 

[Anonymous]

Hello,

The averages of the weights are calculated by store.

For example, in Store A, the average weight of product a is (10+11)/2 = 10.5, and the average weight of product b is 12. In this instance, 10.5 < 12, it counts as 1 under average(a) < average(b). Does this make sense? @Ashish_Mathur @lbendlin 

Store	Product	Time stamp	Weight
A	a	12-Mar-20	10
A	a	15-Mar-20	11
A	b	15-Apr-20	12
B	a	15-May-20	9
B	b	15-Apr-20	9

[Ashish_Mathur]

Hi,

There could be 100's of products in each store.  If there are 100 products in a store, then are you expxecting the average weight of each product to be compared with the other 99?  I really do not know the final output you want (especially when the number of products per store are quite many).

[Anonymous]

Hi @Ashish_Mathur,

Yes there are many products in each store, but each time, I am only comparing the average weight of two products. I am hoping that I can control which two products with two filters. 

However, if filtering is not possible, then hard-coding which two products to compare is an acceptable workaround (I just can't even figure out how to do this!).  

[v-kelly-msft]

Hi  @Anonymous ,

Create a table as below:

Then create 4 measures as below:

count_average(a) < average(b) = 
CALCULATE(DISTINCTCOUNT('Table'[Difference]),FILTER('Table','Table'[Difference]<0))+0

count_average(a) > average(b) = 
CALCULATE(DISTINCTCOUNT('Table'[Difference]),FILTER('Table','Table'[Difference]>0))+0

count_average(a) equals average(b) = 
CALCULATE(DISTINCTCOUNT('Table'[Difference]),FILTER('Table','Table'[Difference]=0))+0

Measure = 
SWITCH(SELECTEDVALUE('Table (2)'[Column1]),
"average(a) > average(b)",'Table'[count_average(a) > average(b)],
"average(a) = average(b)",'Table'[count_average(a) equals average(b)],
"average(a) < average(b)",'Table'[count_average(a) < average(b)])

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Thank you @v-kelly-msft, but your "Difference" calculation doesn't seem correct. The difference between products a and b in store B should be 0, but you are showing 9?

[v-kelly-msft]

Hi @Anonymous ,

Sorry for the late reply.

I didnt quite get your point,but difference here is showing 0 not 9..

Check my .pbix attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Ashish_Mathur]

Hi,

How did you arrive at the result of 0,1,1?

[lbendlin]

this smells like a bad case of the Permutitis .

you have three values of a - that means you have six different ways of computing an "average of a"

with b you have two values, ergo three permutations. That gives you 18 comparisons. Is that really what you want?

Hopefully you have some more rules that better describe the ask.