[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

[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:

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.

The workbook for this post is available here.

#### Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

1. yauwy says:

Good guide but I realized there’s a slight limitation to this solution.

Even if the DAX expression is set to [Pct Sold Smart] = IF(HASONEVALUE(Colors[Color]),[Raw Pct Sold],[Shape Pct Sold])

It would still return [Raw Pct Sold] on the Shape Level if say there is only one single coloured rectangle being sold.

e.g. take out the blue and red records for rectangle

Shape Colour Volumn
Sphere Red 20
Sphere Blue 10
Sphere White 5
Sphere Red 30
Rectangle White 25
Rectangle White 20

It will return:
Rectangle 45 150 300 30%
White 45 150 300 30%

Which should really be
Rectangle 45 150 300 15%
White 45 150 300 30%

Is there a workaround?

1. powerpivotpro says:

Excellent question! I just replaced HASONEVALUE with ISFILTERED:

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

I haven’t had time to test this out in your scenario but I suspect ISFILTERED was invented almost precisely for the situation you described. Will you try it out and report back?

2. yauwy says:

Thanks! Looks like that did the trick!

But as I was browsing through MS help documents, I came across another function HASONEFILTER and is debating whether it should be used in this case instead.

They both yield the same result. What do you think?