All articles with 'FILTER()' Tag

Distinct Count & Blanks – Power Pivot Real Life Example

Published on Feb 11, 2013 in Power Pivot
Distinct Count & Blanks – Power Pivot Real Life Example

When it comes to analyzing business data, managers are always asking, “so how many distinct x each y is doing?”

And that sends us, data analysts & reporting professionals running from pillar to post figuring out the best way to do it.

  • We can use variations of SUMPRODUCT, COUNTIFS etc, but the methods are not flexible..
  • We can use VBA, but it would become slow as you add more data.
  • We can use Pivot tables, but it only gives half of what we want ie each y part, but not distinct count of x.
  • We might as well shave our head with a shovel before manually counting values.

And that brings us to 2 distinctly simple solutions:

  • Using Power Pivot & Excel 2010
  • Using regular pivot tables in Excel 2013

Today, lets talk about these 2 approaches & see why they are so better than anything else for distinct count situations.

Continue »