Forum Discussion

pcolson's avatar
pcolson
Cloud Apps
02-07-2022
Solved

Un-weighting an average value

Hello all,

I have what I hope is a simple question.  

We have a database that can be boiled down to the image below.  Each entry has a date (YYYYMM) and whatever the monthly member count is for that entry (Members).

We want an average member count of Q4 (202110, 202111, and 202112), but the only way I can get Sisense to calculate that is:

100 + 100 + 100 + 100 + 200 + 300 / 6 = 150.

However, there are duplicate member counts/YYYYMMs here.  I want it to do it by distinct YYYYMM, like this:

100 + 200 + 300 / 3= 200.

Different months can have identical member counts.

Does anyone have any ideas?

Data Models
  • pcolson's avatar
    pcolson
    02-08-2022

     

    avg([YYYYMM],avg([Average Members]))

     

    I figured it out this morning.  Breaking it by YYYYMM, then taking the average's average (which will always be the same across the same month).

5 Replies

Replies have been turned off for this discussion
  • harikm007's avatar
    harikm007
    Data Warehouse
    02-08-2022

    pcolson ,

    Try this calculation:

    SUM([YYYYMM], MAX([Members]))/DUPCOUNT([YYYYMM])

    -Hari

    • pcolson's avatar
      pcolson
      Cloud Apps
      02-08-2022

      That still gave me the 150 answer, but see below, I figured it out this morning.

      • harikm007's avatar
        harikm007
        Data Warehouse
        02-08-2022

        pcolson ,

        Its great that you got the solution!!

        I was trying to find why the other formula giving wrong result. I created a table with data what you have mentioned in original post. This is the result I'm getting:

        SUM([YYYYMM], MAX([Members]))/DUPCOUNT([YYYYMM]) //Result 100
avg([YYYYMM],avg([Average Members])) //Result 200

        Were you looking for the result 200? or 100, as mentioned in the original post.

         

  • pcolson's avatar
    pcolson
    Cloud Apps
    02-08-2022

     

    avg([YYYYMM],avg([Average Members]))

     

    I figured it out this morning.  Breaking it by YYYYMM, then taking the average's average (which will always be the same across the same month).