Expression Help: Need a filterable bar graph with multiple dimensions displaying averages

[BarryPollock]

Hi everyone,   

I need a bar graph that shows average profit on rims and tire sales (hypothetically) for each car brand grouped by month (picture at bottom)

So imagine on the left of the graph the annual profit per car sold of Hondas each month (12 bars, one for each month) and then next would be the same thing for Toyota, etc.  The height of the graph would be average profit per car.

Ideally this would be both a grouped graph and a stacked graph, but that capability doesn't exist.  So the height would be the sum of the averages.  So, for example, if the company averaged $1,000 in rim profits per car, and $100 in tire profits per car, the height of that bar would be $1,100 in wheel profits per car (not $550, which is what the average function would give me).

I want a filter that would show the average if it was just Rims, just Tires, or both Rims and Tires.

Here's an example of a piece of the table:

BRAND  TYPE  PROFIT UNITS  MONTH
Honda    Rims  $10,000  10   March
Honda    Tires     $1,000  10   March
Honda    Rims  $12,000  12   March
Honda    Tires         $600  12   March
Nissan    Rims  $10,000  10    April
Nissan    Tires     $1,000  10    April
Nissan    Rims  $26,000  13    April
Nissan   Tires      $1,950  13    April

I wrote this expression, which gives me what I want, except that the filters don't operate when it is written this way:

Sum({<TYPE={Rims } >}PROFIT) / Sum({<TYPE={Rims } >}UNITS) + Sum({<TYPE={Tires } >}PROFIT) / Sum({<TYPE={Tires } >}UNITS)



Note:  I created this report in another application and was able to work around this by adding a row called "total" under TYPE which would be the subtotals.  Then, in the application they could select Rims, Tires, or Total and I used a feature that forced the user to always have exactly one choice selected.


Thanks!

[BarryPollock · Accepted solution]

Hi all,  a coworker was able to help.  This worked:

COUNT(DISTINCT TYPE)*Sum(PROFIT)/Sum(UNITS)



Since the number of Units was recorded for both Rims and Tires, it doubled the number of units.  In my actual table I have four types, so it quadruples the total.  If I simply multiply by 4, then the values are off once the filters are selected.  The COUNT(DISTINCT TYPE) takes this into account.