Straight Chart Table - Find the true Average in Total Row

[Anonymous]

Greetings Experts,

I have a simple chart table that has 1 dimension.  Claim Paid Period.  I have 3 expressions.  Total Paid Amount, Member Count and PMPM ( Per Member Per Month ).  See picture below.  Total Paid Amount is Sum(Total Paid Amount).  MemberCount is Sum(Member Counter) and PMPM is Total Paid Amount / Member Count.  I need the value that is currently in box D2 to be average of the total paid amount / total member count.  Cell E2 has the correct amount that I am showing calculated in Excel.   D2 has the average of the averages.  I have tried setting Total Mode to Expression Total and it does not work either.

This chart is dynamic based on as many as 10 user filters.  The average or averages is off significantly the more your drill down in the data.  My actual application is far more complicated but if you have a solution to this, I can adapt.

Thanks is advance for any help you can provide

[sunny_talwar]

May be this formula:

=Avg(Aggr(Sum([Total Paid Amount])/Sum([Member Counter]), [Claim Paid Period]))

HTH

Best,

S

[Anonymous]

From what i can tell, the Total Mode is set to Average of Rows in your chart for the third expression (PMPM). Can you confirm that Expression Total is selected for the third expression...also, can you try sum([Total Paid Amount]/sum([Member Count]) for your third expression?

[sunny_talwar · Accepted solution]

The above expression is not going to work. Just tested it out. PFA a document within which you can find a working solution.

Best,

S

[Anonymous]

Thanks for your help!  You have fundamentally helped me to understand what I need to do.  I was able to duplicate your solution in my application.  My app uses two fact tables with the Claim Paid Period in one and the Enrollment count in another.  The member count expression was not totaling correctly.  When I selected more data than 1 month, I was getting a distinct total of members rather than the total of the rows as the Expression Total.  When I selected, sum of rows, than the average did not calculate correctly in the total row for the PMPM.  I need to come up with a new way of totaling the member count.

[sunny_talwar]

I am glad I was able to help you out.

Best,

S

[#7]

Did you ever get this resolved? I'm working on something similar and would love some ideas.

Thanks

John

[Anonymous]

Sunny T is the master.  I encourage your to start a new thread with your issue and I am sure he and the other experts will help you find your solution soon.  I was able to solve my problem by reviewing the sample document Sunny provided above.  Have you reviewed that?