Forum Discussion

Anon29's avatar
Anon29
Icon for Helper II rankHelper II
3 years ago
Solved

Average 1 and 0 column

Hi all,
I am needing help to work out the average for a column that just has either a 1 or 0 per row.

I am needing the daily average of the sum of all the "1" values excluding any "0" values.

I am also needing the weekly average as well of the sum of all the "1" values.

Been playing with AverageX but just can't get it to work.
Thank you.

Need Help
  • Arul's avatar
    Arul
    3 years ago

    Anon29 ,

    I am not sure whether I understand your ask correct or not but can you try this by making necessary changes in the formula based on your dataset?

     

    Average of 1s = 
VAR _sumof1s = 
CALCULATE(
    SUM('Average Test Table'[Values]),
    'Average Test Table'[Values] = 1)
VAR _countoftotal1s = 
CALCULATE(
    SUM('Average Test Table'[Values]),
    'Average Test Table'[Values] = 1,ALL('Average Test Table'))
RETURN DIVIDE(_sumof1s,_countoftotal1s)

     

    Thanks,

    Arul

3 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    3 years ago

    Hi Anon29 

    I am needing the daily average of the sum of all the "1" values excluding any "0" values.

    If you summed all fo the 1 values and get the average, wouldn't the average be just 1? The average is technically sum of al 1/count of all 1.

    • Anon29's avatar
      Anon29
      Icon for Helper II rankHelper II
      3 years ago

      Yep that is right if I am looking at just the 1 day. If I was to look at say 2 days, then I'd be dividing by the sum by 2.

  • Arul's avatar
    Arul
    Icon for Super User rankSuper User
    3 years ago

    Anon29 ,

    I am not sure whether I understand your ask correct or not but can you try this by making necessary changes in the formula based on your dataset?

     

    Average of 1s = 
VAR _sumof1s = 
CALCULATE(
    SUM('Average Test Table'[Values]),
    'Average Test Table'[Values] = 1)
VAR _countoftotal1s = 
CALCULATE(
    SUM('Average Test Table'[Values]),
    'Average Test Table'[Values] = 1,ALL('Average Test Table'))
RETURN DIVIDE(_sumof1s,_countoftotal1s)

     

    Thanks,

    Arul