Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need to create a summary table to compare Customers to their respective regions, markets and overall by the potential of what each customer has purchased in the current fiscal year.
Here's an example that might explain it better.
Customer | Potential by Market | Potential by Region | Potential Overall |
---|---|---|---|
ABC | 60 | 20 | 50 |
DEF | 20 | 20 | 30 |
TOTAL | 30 | 70 | 60 |
So that is how I would like my finished table to look.
The problem is with calculating the potential.
The way they want the potential calculated is to ONLY count the potential if the Region/Market/Overall average of sales per commodity is greater than the specific customer average of sales per Commodity. I already have this table set up in one of the tabs, but it does not summarize and it set to calculate based on the selection of the Customer. See example below.
With Customer ABC selected
Commodity | Customer Avg | Market Avg | Market Potential | Region Avg | Region Potential | Overall Avg | Overall Potential |
---|---|---|---|---|---|---|---|
Apples | 10 | 20 | 10 | 40 | 20 | 60 | 50 |
Oranges | 30 | 80 | 50 | 10 | 0 | 20 | 0 |
Bananas | 50 | 40 | 0 | 20 | 0 | 10 | 0 |
TOTAL | 60 | 20 | 50 |
As you can see, the totals from this chart's potential, matches the summary chart. That is basically what I need to do. But every time I try to do a formula for it, it just sums the total of everything minus the total of everything and then gives me 0 for most things if the total customer avg sales is greater than the total avg sales for region or market or overall.
I have tried using Aggr() functions but it doesn't want to exclude the negative values like I need it too.
I've even considered creating the Avgs in a table and putting the values in a field that I can work with.
Any help would be greatly appreciated!
Advanced aggregation is probably the way to go, something along the lines (example for Market Potential):
=Sum( Aggr( YourSecondTableMarketPotentialExpression, Customer, Commodity))
I tried that before, but it just gives me $0 for all Commodities.
Here is the formula for my potential in the second table.
Num($(vMarket_SalesAvgAnnualized)-$(vAvgSalesPerCustomer) )
<=0,'',
Num($(vMarket_SalesAvgAnnualized)-$(vAvgSalesPerCustomer) )))
And what is the definition of your variables?
I think it would be easier to understand your setting and help if you could post a small sample QVW? Would this be possible?