Dollars or Units?

Yesterday was 'Talk Like a Pirate Day'. I didn't post this yesterday because I was going to try out some sweet pirate pick up lines on my wife and didn't want to spoil her amazement at my smoothness...

Which means that I risked a mutiny because you had to wait a day for the bounty of my treasure of knowledge.

Just like a good pirate can choose the best weapon - Cutlass or Blunderbuss - you need to give your user a choice between the best information to run their business. Today, we'll learn how to give them the option of seeing their sales in either dollars or units all in the same chart.

Luckily, this even easy enough for an analyst with a grog hangover.

Step One - Create Your Chart

You can do this without any help from me. If you can't, start reading from the beginning of my blog.

Step Two - Expressions

Create two expressions, one for the sum of sales and one for sum (or count, whatever makes sense for your data model) of units. For this example:

     Sum([Sales])
     Sum([Units])

Make sure to add in any Set Analysis that makes sense for your presentation.

Step Three - Variables

You'll need two variables for this trick. Name the first one vSalesUnits:

• Check Listed Values and add: Sales;Units
• Check Predefined Values only

Name the second one vSalesUnits2:

• In the Value expression editor, enter the following:

    If(vSalesUnits = 'Sales', Num(Sum([Sales]),'$#,###.'),

Num(Sum([Units]),'#,###.'))

• Make sure that you DON'T start the expression with an equal sign.

Step Four - Button

Create a button that will allow the end user to swap between Sales and Units.

• The text of the button needs to change with the swap; use this syntax:

     =If(vSalesUnits = 'Sales', 'Show Units','Show Sales')

• Use a Set Variable action to set the value of vSalesUnits:

     =If(vSalesUnits = 'Sales', 'Units','Sales')

• Use a lot of discretion in the formatting; this example shows it simply as a text link

Step Five - Chart Expression

We'll use $-sign expansion and the variable vSalesUnits2 as the chart expression:

     =$(vSalesUnits2)

Step Six - Chart Title

The title of the chart needs to reflect the choice between Sales and Units:

     =If(vSalesUnits = 'Sales', 'Sales by Product','Sales by Product (Units)')

Grand Finale
Put it all together in a tasteful way. That means:

• Simple to understand
• Understated
• Doesn't make me mad

A couple of notes:

• When you use number formatting in the variable expressions, you need to use the expression default format on the number tab of the object
• If you're not careful, this kind of trickiness can confuse your users; make sure they are trained
• Don't overuse these techniques
• You can also use this on a page or document level

Epilogue

Some of you may be wondering how my pirate pick up lines worked... Since this blog is rated PG for 'some violence', let me just say that using "wench" in your pick up line is ill advised.

And finally, in the immortal words of Cap'n Slappy

Some days I feels like lootin'. Some days I feels like plunderin'. Some days I feels like beating someone senseless with me fists and forehead. And some days I just feels "not so fresh."
But ne'er a day goes by that I don't feel like getting blistering drunk and playing "Full Contact Scrabble!"
Thar be a triple word score and a belayin' pin to the noggin here fer all!