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.