Power Pivot Principles: Using the ALLNOBLANKROW Function to Calculate The Percentage of Total
13 October 2020
Welcome back to the Power Pivot Principles blog. This week, we consider how to calculate the percentage of a total using the ALLNOBLANKROW.
Last week, we introduced the ALLNOBLANKROW function which returns all the rows except for the blank rows in a table, or all the values in a column, ignoring any filters that may have been applied. From the previous sample dataset let’s revisit Store_ID, which lists five store IDs and their respective names,
and the Car_Sales_Count table which records the sales of car by Date, Store ID and Sales Person.
The above two tables are already loaded into the Power Pivot Data Model and a relationship was previously set up between them, by connecting the Store ID fields in the two tables, viz.
We counted rows using the ALLNOBLANKROW, ALL, VALUES and DISTINCT functions when there is a blank row in the PivotTable:
Now, we want to see the share of Sales by store. We create a measure by nesting the DISTINCT and ALLNOBLANKROW functions inside the COUNTROWS function, viz.
% #Store Distinct:= DIVIDE(COUNTROWS(DISTINCT(Car_Sales_Count)), COUNTROWS(ALLNOBLANKROW(Car_Sales_Count)))
The formula works well, but we can try making it shorter by using the VALUES and ALL functions instead:
% #Store Values:= DIVIDE(COUNTROWS(VALUES(Car_Sales_Count)), COUNTROWS(ALL(Car_Sales_Count)))
This measure returns the same results:
Still, the % #Store Values measure is not the shortest (which mean most efficient!) way to get the result. We can trim it down by keeping the ALL function in the denominator and nesting no other function inside the COUNTROWS function in the numerator:
% #Store:= DIVIDE(COUNTROWS(Car_Sales_Count),COUNTROWS(ALL(Car_Sales_Count)))
We get the same results in three different measures:
As always, simplicity is best!
That’s it for this week!
Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.
