cancel
Showing results for
Did you mean:

# Un-weighting an average value

8 - Cloud Apps

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?

1 ACCEPTED SOLUTION
8 - Cloud Apps

``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 5
13 - Data Warehouse

Try this calculation:

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

-Hari

8 - Cloud Apps

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

13 - Data Warehouse

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.

8 - Cloud Apps

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!

8 - Cloud Apps

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

Community Toolbox

Developers Group:

Product Feedback Forum: