Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
I have a column field named "category"
in this "category" i have the values: "good", "medium", "bad".
1)
I want to see how high the share of the "category" is per each dimension "customer" in percent ("good" 30%, "medium" 60%, "bad" 10%)
2)
Then I want to compare the share of a customer ("good" 30%) compared to the average customer ("good" 70%) and give the text information "the customer x is y % below average in the category z)
I hope I did not express it too complicatedly. Thanks in advance and sorry for the bad wording. 🙂
Use
Sum(Sales)/Sum(Total <Category> Sales)
out below
Customer | Category | Sum(Sales) | Sum(Sales)/Sum(Total <Category> Sales) |
Peter | A | 100 | 25.00% |
Larry | A | 300 | 75.00% |
John | A | 0 | 0.00% |
Peter | B | 400 | 100.00% |
Larry | B | 0 | 0.00% |
John | B | 0 | 0.00% |
Peter | C | 500 | 20.00% |
Larry | C | 900 | 36.00% |
John | C | 1100 | 44.00% |
Hi, What about the attached.
Hi,
[Customer Share]
sum(Sales)/Sum( total <Category> Sales)
Average
avg(total <Category>aggr(sum( {<Sales-={0}>}Sales)/Sum( total <Category> {<Sales-={0}>}Sales),Sales,Category))
Difference
[Customer Share]
-
Average
Advice
=if(Difference<=0,
'Category '&Category &' is '&num(Difference,'#,##0.00%') & ' under average',
'Category '&Category &' is '&num(Difference,'#,##0.00%') & ' above average'
)
Note that the 'Difference' expression use the previous expression names of [Customer Share] and [Average] and the 'Advice' expression uses the [Difference] expression name.
See attached image of result