[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:
Sales Table – the Numerator of our Pct Sold Measure
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:
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.
I Created Lookup Tables: Colors and Shapes
Then I create relationships between those two tables and my other three existing tables:
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
[Total Stock] =
[Raw Pct Sold] =
[Sold] / [Total Stock]
[Max Sales Amt by Shape] =
[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:
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.