skip to Main Content

 
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.

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.

This Post Has 3 Comments
  1. 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. 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?

Leave a Comment or Question