image

[Pct Sold Smart] Measure Matches [Shape Pct Sold] at the Shape Level (Orange Cells) But Matches [Raw Pct Sold] Down at the Color Level

Quick Post Today

I’m halfway on vacation today in Florida, visiting family, so I will keep this brief.  I saw this question today on the forums.

Sales and Stock Tables

The question is pretty simple – I have a Sales table and a Stock table:

image

Sales Table – the Numerator of our Pct Sold Measure

image

Stock Table – the Denominator of our Pct Sold Measure

And we want a measure that returns Percentage Sold. 

But there’s a twist:  at the Shape level, we want to use something other than Stock as the total.  We want to use another cap, a Shape-Level cap:

image

At the Shape Level, We Want to Use These Values (Instead of Stock) as the Denominator

First Step:  Lookup Tables

Before I can do anything, I need to create Lookup tables for Colors and Shapes.  This is a lot like the Budget vs. Actuals posts I’ve done in the past, feel free to look at those if something isn’t clear here about my intent.

image  image

I Created Lookup Tables:  Colors and Shapes

Then I create relationships between those two tables and my other three existing tables:

image

After Relationship Creation (I Added Colored Arrows to Indicate the Direction
that Filters Flow –From Lookup Tables to the three “Data” Tables)

Now for the Measures

[Sold] =
SUM(Sales[Volumn])

[Total Stock] =
SUM(Stock[Stock])

[Raw Pct Sold] =
[Sold] / [Total Stock]

[Max Sales Amt by Shape] =
MAX(ShapeMaxSell[Max])

[Shape Pct Sold] =
[Sold] / [Max Sales Amt by Shape]

and then the “smart” measure:

[Pct Sold Smart] =
IF(HASONEVALUE(Colors[Color]),[Raw Pct Sold],[Shape Pct Sold])

Which really just says:

“if I’m in a context where there’s just a single Color, that means I am at the Color level, so just use the raw measure.  If I’m in a context where there is MORE than one color value, that means I am at a total level for Color, and since I know that totals for Color is the same thing as the Shape level in this pivot, that’s where I want to use the [Shape Pct Sold] measure instead.”

And results in:

image

What About Grand Total?

Maybe that 61.4% isn’t what we want.  I’m out of time to address the different approaches here, but we could return BLANK() for grand totals, we could change my [Max Sales Amount by Shape] measure to be something other than MAX(), we could return a completely new third measure, etc.

Download the Workbook!

The workbook for this post is available here.