Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a table in data model with client ID & client name, Company ID & Company Name, Product Group, Product Sub-Category, Company Revenue, EE Segment. A company ID has more than one Client ID and each client ID will have more than one Product Group and many Product Sub-Category (under each product group). Each record has a Revenue by its company (called Company_Revenue) which is an aggregate of all client ID (under that company) all together for the product group and sub-category. For determining Revenue by each client ID i can use following expression: Sum(Aggr(Avg([Total_Company_Revenue__c]), [Client ID]))
Question 1) I want to calculate the Quartile for each client ID based on its revenue. I used the below expression in the measure of the table. I am not sure if this is right way and it gives different quartiles for same client ID which is wrong. Each client ID should have only one quartile based total revenue for all Client IDs.
Syntax:
'Q'&if(Sum(aggr(Avg([Company_Revenue]), [Client ID])) <= fractile(TOTAL aggr(Avg([Company_Revenue]), [Client ID]), 0.25), 1,
if(Sum(aggr(Avg([Company_Revenue]), [Client ID])) <= fractile(TOTAL aggr(Avg([Company_Revenue]), [Client ID]), 0.50), 2,
if(Sum(aggr(Avg([Company_Revenue]), [Client ID])) <= fractile(TOTAL aggr(Avg([Company_Revenue]), [Client ID]), 0.75), 3, 4)))
Question 2) I want to achieve Quartile for each client ID based on Total count for Product Sub-Category sold under Client ID. I am not sure of it's syntax.
I would really appreciate any help and leads on calculating quartile in this situation
Thanks
Monica
Below is the snapshot of data from my table and an attachment of the table itself:
Client Name | Client ID | Company Name | Company ID | Product Group | Product Sub-Category | Tenure | Total Company Renenue | Company Count | Client Count | Client Quartile | Client Revenue |
Kreller Group Inc | 1139 | Kreller Group Inc. | 111000 | Payroll | Payroll | 10+ Yrs | $5k-$10k | 1 | 1 | Q3 | 5904.94 |
Kreller Business Information Group Inc | 38952 | Kreller Group Inc. | 111000 | Benefits | ACA | 10+ Yrs | $5k-$10k | 1 | 1 | Q4 | 0 |
Kreller Business Information Group Inc | 38952 | Kreller Group Inc. | 111000 | HR | ACA Year End | 10+ Yrs | $5k-$10k | 1 | 1 | Q4 | 0 |