Charts & Data Model

[regowins]

Hi,

I need some guidance with the following.  In the app I am working on I have a Client dimension linked to a fact table of metrics. The problem is I also have the Industry average in the client dimension linked to the same fact table within metrics.  For example, the Client dimension has, Client A, Client B, Client C & Industry Avg.   Generally, you calculate the Avg of the clients within the charts instead of it been pre-calculated, but this how the customer needs it for now.    How do I handle this is charts and trends where the customers wants to do comparisons of a single or multiple clients to the client average when clicking on a client?  The only way I can think of is to pull the client average dimension and metrics into a separate table within the data model. Are they better ways to do this?  Attached is visual example of what I am referring to.

Thanks for the help!

[#2]

Hello.

I'm not really sure if I understand your problem correctly, but I think the very correct method is to calculate "Industry Avg" using expression instead of pre-calculate it - as you said. But if client really sticks to idea of pre-calculating this value, you can use the "Industry avg" as a member of Client dimension as long as the value for this member is in fact table - as it is normal client...

...BUT you must remember that this will cause problems f.e. with aggregating values on Client dimension - the "Industry avg" value will be added to f.e. sum of sales for all clients if you don't exclude it in expression.

I think for the most correct solution you should use 3 tables - one for client dimension, one for analytics dimension (simple value, avg etc.) and one for fact table (with id of client, id of analytics and value).

[regowins]

Thanks for the feedback. Currently the "Industry Avg" is a member of the client dimension with the corresponding values in he fact table.