I am constantly amazed at how much you can achieve in Power Pivot with a relatively small amount of DAX knowledge.  I was working with a client recently and he wanted me to produce a report to see the following:

1.  The sales of selected products (he would select the ones he wanted to see from a list)
2.  To see what the percentage those selected product sales made up of the relevant categories.
3.   If he selected more than 1 product from different product categories then the share of relevant categories should be across all categories the selected products came from (the aggregate).

At first glance, this seems like a hard thing to do (detect the relevant categories related to the selected products) however DAX is an amazing language.  I have built a demo using Adventure Works to show how easily this can be done.

Set up a Pivot Table

I set up a Pivot Table pointing to a simplified star schema version of Adventure Works data model that has Product Category and Sub Category as columns in the product table.

model2

I added a slicer on the product name and selected 2 products “Classic Vest, L” and “Bike Wash – Dissolver”. image

The sales for these products are shown in the pivot table above along with the relevant sub category name, sub category sales and the % of total sub category. The simple trick to all of these calculations is to be able to determine which sub category the selected product belongs to.

One of the Many uses of Values

Values is a great little function.  It does many things (as explained by Scott from Tiny Lizard here).  I used VALUES as the main function to solve this problem.  Firstly I wrote the following measure so I could “see” what the sub categories were.

Relevant Sub Category =IF(HASONEVALUE(Products[SubCategory]),VALUES(Products[SubCategory]),BLANK())

If the result of VALUES is a single row (ie only 1 value), then it is quite permissible to place that value into a pivot table.  Power Pivot will convert the table (a single row table in this case) to a scalar value for you automatically.  However you must protect the pivot table to prevent the accidental placement of a table with more than 1 row into the pivot – this is not allowed.  To do this you must wrap your formula inside an IF(HASONEVALUE()) construct as you can see in the formula I used above.  The BLANK() alternate result of the IF statement you can see above is optional (and the default), so this parameter can be omitted and it will return the same outcome.

CONCATENATEX

If you are using Excel 2016 or Power BI Desktop, then there is a new function (not available in earlier versions of Power Pivot) that allows you to take a table of values and concatenate those values into a single scalar value.   In the Grand Total Row of Column E in the Pivot Table above, the VALUES(Product[SubCategory]) has 2 values in the current filter context, hence VALUES will return a single column table with 2 rows (one for Vests and one for Cleaners).  A Pivot Table cannot render a 2 row table into a cell, but it is possible to concatenate the values from each row into a single scalar value (with Excel 2016 and Power BI).

Modifying my original formula, I added the following extra CONCATENATEX code into the formula.

CONCATENATEX(VALUES(Products[SubCategory]),Products[SubCategory],”, “))

The final formula for “Relevant Sub Category” is therefore

=IF(HASONEVALUE(Products[SubCategory]),
VALUES(Products[SubCategory]),
CONCATENATEX(VALUES(Products[SubCategory]),Products[SubCategory],”, “)
)

image

As you can see above, now when there is more than 1 value in the current filter context, the formula returns all of those values into a single scalar string that can be inserted into a pivot table.

Sub Category Total Sales

The great thing about the measure “Relevant Sub Category” above is that you can “See” that what is happening behind the scenes with the VALUES function.  I find this one of the hardest things for Excel folk to learn is how to “visualise” table functions.  I find that writing measures to help you “see” the contents of table functions really helps Excel folk move forward.

I then wrote the measure Sub Category Total Sales using the VALUES formula but this time I passed the table as a filter inside CALCULATE as follows.

Total Sub Category Sales = CALCULATE([Total Sales],ALL(Products),VALUES(Products[SubCategory]))

The above CALCULATE formula has 2 table filters; an ALL(Products) table and the VALUES(Products[SubCategory]) table.

ALL(Products) is required to remove the current filter context from the selected products in the pivot table.  Without this ALL function, the total would simply be the total sales for the selected products.

The second table function VALUES(Products[SubCategory]) (as we already know from above) produces a table of Sub Categories that is relevant to the products selected in the current filter context.  When there is a single row in the pivot table there is only 1 relevant sub category.  But on the grand total row in the pivot table, there are currently 2 relevant sub categories.  This table of relevant sub categories is passed to CALCULATE and CALCULATE applies this table as a new filter to the data model before propagating the filter from the product table to the sales table before finally adding up the sales for those relevant sub categories.

The final formula is now simple to create as follows:

Products % of Sub Category Sales = DIVIDE([Total Sales],[Total Sub Category Sales])

Matt Allington

Matt Allington is a Microsoft MVP specalising in Power Pivot, Power BI and Power Query Consulting and Training based in Sydney Australia.

This Post Has 6 Comments

  1. Very slick solution Matt! I like that your post teaches many people many different things! I haven’t seen CONCATENATEX used with VALUES to generate a scalar string before, but I’m already coming up with ideas on how to use it.
    A CUBEVALUE function that lands the entire string into a single cell to populate a graph’s Title / Axis dynamically comes to mind…

    Thanks again for the great post!

  2. Thank you Matt! I’ve been banging my head against my keyboard most of today and yesterday, trying to make a % of sub-category formula come out. Pored through PP&PBI 2e and tried every combination of CALCULATE, FILTER, and ALL I could think of, but nothing worked or wasn’t quickly broken. Finally thought to just check the site, and here you’ve solved all of my problems. Glad you didn’t wait too long to post this! Thanks so much!

  3. Christopher and Hunter, I’m glad this was useful. I don’t always know what level of material to share and it is hard to get the level right for everyone. I am glad this has been useful for you both – and hopefully others too.

  4. Hi Matt, it’s always a pleasure to read your very methodical approach in solving a specific problem. I just want to add that if the ‘SubCategory’ field isn’t in the product table you’ll have to use another approach. In the adventure works database there usually is a snowflake model where a many-to-one relation exist between product and subcategory and Category. In that case fields from the product table can not automatically filter fields from the subcategory table so you would have to use a measure something like this:

    Total Sub Category Sales =
    CALCULATE (
    [Total Sales],
    ALL ( Products ),
    CALCULATETABLE ( VALUES ( Products[SubCategory] ), product )
    )

    I know you already know this I just wanted to add this comment for the benefit of other readers that don’t and want to try out a version of adventure works that hasn’t been changed to include the subcategory field in the product table.

    Best regards.

  5. Thanks Jess. I have actually deployed this exact pattern before with the same customer, but I simplified it for this post. I have been using my simplified star schema for so long that I forgot that standard Adventure Works has a sub category table. Thanks for sharing

Leave a Comment or Question