The Matrix has got you, Neo, except that if you know how to dodge bullets using nifty Excel formulas!
Talking about Matrix and numbers (and also my favorite movie!), for years I have been using SUMIFS or GETPIVOTDATA formulas whenever I needed to SUM values from another table or pivot based on some condition. It worked quite well EXCEPT when I needed to look up SUM for the huge datasets or some complex filter combinations among other things. That is where the third child – CUBEVALUE – was born.
Once CUBEVALUE formula came out about 10 years ago along with PowerPivot, I was delighted because it had filled the gap that was missing in SUMIFS and GETPIVOTDATA formulas for certain usage scenario. Below I write about the common usage of all 3 formulas and their PROs and CONs based on my personal experience. I list various scenarios and then rate them with score 1 to 3, with 1 being lowest and 3 the highest respectively.
USAGE SCENARIOS
Below are details regarding each Usage Scenario:
- Small Dataset – for smaller datasets its much quicker and simpler to use SUMIFS. For doing GETPIVOTDATA, you need to first setup a pivot in one of the background tabs of the Excel and then reference formulas to that pivot. For CUBEVALUE, it would become a bit tedious to load PowerPivot backend data, then creating Measures to be referenced by the formula.
- Huge Dataset – for a big dataset with more than 1 million rows, currently Excel will be very slow and will not handle such data volumens in a normal Table format. Therefore, having SUMIFS is almost not possible, as it will kill or freeze Excel instance. If the a huge dataset is loaded in a compact Pivot format, then GETPIVOTDATA can be used. But if you need to get different filters or views in the Pivot, you would need to duplicate Pivots in order to be referenced by GETPIVOTDATA, which will explode Excel file size. Using CUBEVALUE with large Datasets is optimal. Power Pivot backend saves data in a very compact way. There is no need to create different pivot tables or views to be referenced for the CUBEVALUE formula.
- If we have a complex formula that SUMs extensive filter combinations (such as customers in Australia, Small Customers, in south Sydney, IT Branch, etc…) then your GETPIVOTDATA will require you to create very detailed background pivots to reference that formula, which is not idea. SUMIFS is better in this case, but the formula writing and readability can get quite complex. The best way is to use CUBEVALUE formula, as it is very readable and can use any extensive filter combination possible.
- Formula Simplicity/Readability – the winner here is CUBEVALUE as without knowing much of the dataset, its easy to right away understand what is being calculated. CUBEVALUE has an advantage over GETPIVOTDATA due to Intellicense while writing the formula. SUMIFS is just not user-friendly at all in this case.
- Calculation Speed – in my experience SUMIFS is always the slowest formula especially with larger datasets and multiple filter conditions. CUBEVALUE always has few seconds delay, while GETPIVOTDATA is the fastest one.
- Ease of Writing – the easiest and quickest to write is GETPIVOTDATA, as there is an option in excel to “Generate GETPIVOTDATA” automatically whenever you point to a cell in a pivot. SUMIFS can be confusing when you forget what goes first Values or Filter conditions.
- Calculating Ratios On The Fly – here the best choice is CUBEVALUE, as it can easily work with various denominators on the fly to get any ration. SUMIFS is also working ok for this reason, but usability is a bit more complex. GETPIVOTDATA can be used for ratios, but your backend pivot table has to be setup exactly sub-totalling the denominators otherwise it becomes tedious.
- Complex measures – the clear winnner is CUBEVALUE, which using PowerPivot’s DAX magic it is possible to create very complex calculations /measures, which can be referenced in the CUBEVALUE formula. Both GETPIVOTDATA and SUMIFS are not ideal for complex calculations.
- Backend Setup – SUMIFS doesnt requite any additional backend setups other than a flat table. GETPIVOTDATA requires you to setup multiple background pivot tables that can be referenced by formulas. CUBEVALUE requires to load PowerPivot backend and setup the Data Model which can be complex or time consuming.
CONCLUSION
Finally, by now you should know how to dodge bullets using the 3 formulas, and the winner of which is the CUBEVALUE! Of course, in different scenarios you should be using different formula, but overall CUBEVALUE has the most usage advantages.
Do you agree or what do you think? Please leave a comment below.
#Excel #cubevalue #sumifs #getpivotdata #formula #matrix #dataempower
