Issue with Small Multiple

#1

Using example sales data with two hierarchies, Region and Category.

Region:
North
East
South
West

Category:
Shoes
Hats
Shirt
Pants

I am using a small multiple that displays Sales $ By Category for every Region
so the north section looks like:

Region North

Shoes 1K
Hats 3K
Shirt 8K
Pants 10K

This works fine, however, next to the $Amt, I also need to display the % each category is of the Pants Category.
I create a calculated measure PantsOnlyAmt:
if ($Category$ == ‘Pants’)
{ return $Amt$;}
else
{ return 0;}

And another calculated measure: Amt as % of Pants Amt
$Amt/SUM($PantsOnlyAmt$)

Because the PantsOnlyAmt only has a value in the Pants line, I need to do the SUM so that I can access that amount from all the other categories.
This works correctly in a dashboard with a table and a Region filter, but it is not working in the Small Multiple because the SUM($PantsOnlyAmt$) seems to be retrieving all of the Pants categories across ALL regions.

Is there a way around this behavior? I’m guessing I could create the calculated PantsOnlyAmt in the data cube and it would work, but wondering if there is another way in the small multiple. Can anyone explain what is going on behind the scenes with filtering in the Small Multiple? Since the Region field has to be a the ROW level, is there a way to specify in my PantsOnlyAmt calculation to only take the records with the “current region”?

Hope that makes sense with the strange example data situation. Thanks.

#2

Actually, doing the PantsOnlyAmt calculation in the data cube doesn’t work either because it only shows the value for the Pants line then. I still need to do the SUM which goes back to my original problem of the Small Multiple takes the sum from ALL the regions, not just the one it is filtering and iterating over.

#3

Instead of using the division formula, could you quickly try using the PERCENTTOTAL formula?

https://www.dundas.com/support/learning/documentation/analyze-data/formulas/standard/percent-of-total

Does using that formula also give you the same problem with the percentages being off (since SUM is behaving differently then expected)

#4

I don’t see how this will work because I am not getting a percentage of total within one value, I need the actual equation of Amt/PantsOnlyAmt. I need to SUM the PantsOnlyAmt since there is only a value for the Pants row. Thank you for the suggestion, please let me know if I am misunderstanding what you are saying.

#5

Actually, it was me who misunderstood the situation. One thing that just sprung to my mind - there is a second parameter you can pass when doing a SUM, which is the alignment hierarchy.

https://www.dundas.com/Support/learning/documentation/design-view/formulas/standard/totals-sum#h3-parameters

What the alignment hierarchy will do is essentially regulated which way the sum goes. So if you would like to get the sum across the category axis, you would do something like:

SUM($PantsOnlyAmt$, $Category$)

Otherwise, if this doesn’t work, one other question - are you filtering by region on the small multiple as well?

#6

No, but making it Amt/SUM($PantsOnlyAmt$, $Region$) did work! Thank you so much! I am not filtering on Region but I am using it as my repeater in the small multiple so it is filtering each small multiple by Region, so it is essentially filtering by Region. The only thing that wasn’t filtering was the SUM($PantsOnlyAmt) because it was including the total from all regions. By adding the Region as the alignment hierarchy, this issue was solved. Thanks again, I really appreciate the help!