cancel
Showing results for 
Search instead for 
Did you mean: 

Un-weighting an average value

pcolson
8 - Cloud Apps
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).

pcolson_0-1644269338393.png

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

pcolson
8 - Cloud Apps
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).

View solution in original post

5 REPLIES 5

harikm007
12 - Data Integration
12 - Data Integration

@pcolson ,

Try this calculation:

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

-Hari

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

harikm007
12 - Data Integration
12 - Data Integration

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

 

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
8 - Cloud Apps
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).

Voice of Customer
Impact Business Intelligence (BI) and Analytics Market Research

Click Here & Complete Dresner's Wisdom of Crowds® Market Study Survey Today!

Community Toolbox

Recommended quick links to assist you in optimizing your community experience:

Need additional support?:

Community Support Request

Don't miss out:

Earn a BETA Launch Challenge Badge!