How to calculate the average difference between two measures doing a count on text values

[Anonymous]

Hi, 

I have two measures currently doing a count on a text field shown below

Total Apples = COUNTROWS(FILTER('Table',[Category]="Apple"))

Total Pears = COUNTROWS(FILTER('Table',[Category]="Pear"))

I am trying to get a average difference between the two measures which is to be used in a card visual to show the average difference for whichever period that have selected in the date range slicer.

So just to put it as simply as I can... if the average amount of Apples is 29 and the average number of Pears is 10 the difference would be 19 but I can only seem to get it to work on totals rather than averages.

Thanks in advance!

[v-jianboli-msft · Accepted solution]

Hi @Anonymous ,

Based on your description, I have created a simple sample like this:

Please try:

Difference =

var AverageApples = COUNTROWS(FILTER('Table',[Category]="Apple"))/(DATEDIFF(MIN([Date]),MAX([Date]),DAY)+1)

var AveragePears = COUNTROWS(FILTER('Table',[Category]="Pear"))/(DATEDIFF(MIN([Date]),MAX([Date]),DAY)+1)

Return AverageApples – AveragePears

Output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,

This worked perfectly, thank you!

[amitchandak]

@Anonymous , this is count, Avg based on what , Say Date, Month , Region, City etc.

Based on the need

Averagex(Values(Table[Month Year]), [Total Apples]) - Averagex(Values(Table[Month Year]), [Total Pears ])