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.
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.
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.
The final formula for “Relevant Sub Category” is therefore
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])