Matrix Grid multiple rows not expected
In the table shown, there is only one value (quintile) for each row (State) and column (Type). Why does each state have multiple rows? How can I get it to only show populate cells?
I tried to enable the subtotals, which produces the effect I want~ almost. but I cannot show only subtotals nor can I add color to them.
Actually - the result is correct. By adding the 'quintile' attribute (blue chip) in the color drop zone, you're creating a multiplicity of values in the result - because there is a cross relationship between state and quintile for every 'type'. You can see the effect, by also adding the 'quintile' blue chip to Rows (and then try moving it to Columns for an interesting effect.).
To get the effect you are after, IMO, you need to color the values with a value.
The quick version is to use the quintile value itself (orange chip) and banded conditional formatting. Dropping the chip into colors creates a gradient usually out of the box. But you can tweak that into fixed band conditional formatting through the CF advanced dialog (from the design tab in the ribbon, choose 'Conditional Formatting', then choose Bands > Fixed). This may work fine depending on how the quintile values aggregate in your model.
In the event that doesn't work, try out the "Value" choice in CF for 'value driven colors.' It more advanced, requires some formulas, but is significantly more powerful and self-managing in the long run.