Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

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!
Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    4 years ago

    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.

3 Replies

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi,

       

      This worked perfectly, thank you!

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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 ])