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]="...
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.

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!