Un-weighting an average value

[pcolson]

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?

[harikm007]

@pcolson ,

Try this calculation:

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

-Hari

[pcolson]

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

[harikm007]

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

Oh my gosh that's so embarrassing.  Yes I meant 200.  I have revised the original question.  Thank you so much for jumping in and helping out!

[pcolson · Accepted solution]

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