Cross-Filtering in Slicers with Cube Formulas

April 8, 2014

Back from Paradise, Here’s a Quick Tip

Funny thing about vacations is that everyone is waiting on you when you get home.  But man, what a vacation.

Anyway, I’m juggling my final edits to Alchemy, spending two full days with a client, AND teaching a class on Wednesday/Thursday, so today’s post will be brief, but hopefully still useful:

Power Pivot Slicers and Cross Filtering with a Pivot

PivotTable with Two Slicers.  Gender Slicer “Cross Filters” the Customer Name Slicer,
As Expected.  All is Right with the World.

Now we convert the pivot to cube formulas:

Converting Pivot to Cube Formulas

And look what happens to the cross filtering:

Cross Filtering in Slicers is Not Working with Cube Formulas


Read the rest of this entry »

Turning “OR” Slicers Into “AND” Slicers

April 3, 2014


In this Report, We Are Only Seeing Customers Who Have Purchased
Both Accessories AND Clothing During 2004

A Post From Oceanside!

imageYeah, I’m on vacation (my first real vacation in 5+ years), so why am I writing a post?  Well, it’s before 9 am, the family is still sleeping in, and I honestly loved the idea of slipping out to write a post while looking at the ocean. 

The truth is I LOVE writing these posts – in some sense they represent Peak Fun for me, especially when they can be written at a relaxed pace with no outside pressures.  In the future, maybe I will take vacations for the express purpose of writing.  (That sounds surprisingly good to me actually).

Slicers – The More You Select, the More You “Get”

Read the rest of this entry »

Net Promoter Score: Fiendishly Simple in PowerPivot! (Caution: Post Contains 26 Movie Quotes)

March 25, 2014


Net Promoter Score in Power Pivot

Net Promoter Scores Are Fiendishly Simple to Calculate in Power Pivot

What is “Net Promoter Score?”

Fundamentally, it’s a measure of how many of your customers love you, minus how many of them dislike you.  Hence the name – Net Promoter Score.

WARNING:  I am personally no expert here.  I am doing my usual thing:  take a small amount of knowledge and wield it like a battle axe.  I was helping a client today (Monday) with this, and am writing about it a mere three hours later.  But I figure there are lots of people out there who need to do this sort of thing, and THEY get what it all means.  So allow me to share how EASY these calcs are in Power Pivot. describes NPS as:

Read the rest of this entry »

TopN as viewed by DAX Table Queries

March 13, 2014

Guest post by Scott Senkeresty


Get it?  “Median?”  SO Funny!


Rob is on-site with a client this week, so the reins, mic, baton or other appropriate metaphor gets handed to me today.  We get to today’s topic by means of a discussion on calculating a median in DAX:

Scott:  That sounds easy.  Just use TopN to grab half the numbers in ascending order, then another TopN against descending values to grab the final value(s)  (Glossing over odd vs even number of data rows)
Rob: What are you going to do about ties?
Scott:  <Blank Stare>

It turns out that calculating a median in DAX is pretty tricky.  Rob contends that sneaky street fighting tactics are required to deal with ties…where in my heart of hearts, I believe an elegant solution exists.  Hopefully we can get to the bottom of that in a future blog post.

Read the rest of this entry »

Automatically create data dictionary for your Power Pivot model

March 11, 2014

By Avichal Singh

There are many reasons why you would want to document your Excel Power Pivot or SSAS Tabular model in an effective manner:- to better understand your model yourself as it moves from simplistic to advanced, facilitate the use of your model by other users, ability to transition model development to another person and a few more.

Diagram View is good, but you can do better Smile. You could automatically build a data dictionary – listing all the Dimensions, Attributes, Measures and their Description – and maintain it with almost no effort at all. Watch the video and read the rest of the article to find out how.

Video walkthrough showing how to automatically create data dictionary for your Power Pivot model

“CONTAINSX” Revisited: What WAS the Match?

January 28, 2014

Building on a Popular Technique

Power Pivot Substring Match/Contains Grouping Column

Last week’s post on “CONTAINSX” proved to be quite popular.  In the comments, Sasha provided an alternate formula that used FILTER instead of SUMX.  Honestly I have a been of a “fetish” for SUMX – after all it IS the 5-Point Palm Exploding Function Technique – so at first I was like “nice work Sasha but I’m sticking with my SUMX.”

But then “en” asked if we could write a formula that reported what the matching keyword actually WAS – not just whether there WAS a match.

And then, Sasha’s formula came in super-handy.  A couple of quick mods and we were in business.  Read on for the formula, but first, a quick aside.

Another thing that is easier in Power Pivot than “Traditional” Excel

I really enjoyed the comments (and the emails) we received about the CONTAINSX post.  Here are two of my faves:

Read the rest of this entry »

“CONTAINSX” – Finding if a value in table 1 has a matching value in table 2

January 23, 2014

Finding matching or duplicate or "contains matches" across tables in Power Pivot

We Want to Flag Rows in our Companies Table (on left) When They
Contain a Keyword from our MatchList Table (on right)

These are a few of my favorite things…

Perhaps the only thing that makes me happier than a new “X” function (I still badly want a CONCATENATEX) is “inventing” a new one (like we’ve seen with PRODUCTX).

The other day I was looking at a Power Pivot model and thinking “gee, it sure would be nice to have a CONTAINSX.”

Turns out we can “make” our own CONTAINSX using SUMX.

Does this row’s value in list one also appear in list two?

Read the rest of this entry »

Another Way to Find the Top-Performing “Thing”

January 14, 2014

Best Selling Product, or Date, or Top Performing Player - All Can Be Handled, Including Ties, in Power Pivot!

Pretty Nifty PivotTable

Best Selling Product, or Date, or Top Performing Player - All Can Be Handled, Including Ties, in Power Pivot!

Same PivotTable, Diagrammed/Explained

Revisiting an Older Topic

Read the rest of this entry »

Grand Total Mania! (Totals at Top & Multiple Totals)

January 7, 2014

Let’s start 2014 with a bang…

I’ve been dying to write this post for awhile now.  Let’s say you want your grand totals to appear at the top of your pivot, like this:

Grand Totals at the TOP of the Pivot?  Yep, no problem.

Grand Totals at the TOP of the Pivot?  Yep, no problem.

Typically you’d want to do this when your pivot is really “tall” – lots of rows – and you don’t want to force people to scroll down in order to see the grand total.

Pivots do NOT let you display grand totals at the top of the pivot – only the bottom – so a frequent workaround is to write a formula in Excel itself that sums the whole column of the sheet.  Very clumsy, and damn near impossible when you have two fields on rows like above.

How DO You Do It, Then?

The trick is simple – add a dummy table to your Power Pivot data model:

Read the rest of this entry »

A Simple Trick for Combating “Stale” Slicers

December 3, 2013

Help!  My Power Pivot Slicers Don't Auto Update to the Latest Date on Refresh!

Two Weeks of Refreshes Later…  the Report Still Thinks
Nov 15 is What Everyone Wants to See First!

Your Dashboard is Refreshed, But its Slicers are Stuck in “Yesteryear”

This is a trick I’ve been using forever but never blogged about.  Enough of you are now using Power Pivot for SharePoint (PP Server) that its time has come.  And really, it’s relevant on the desktop too.

On the day you first made this report, you selected the most recent Date (or Week, etc.) in the slicer.  And you saved the report.  All was right with the world!

But then, tomorrow comes.  And all of your slicers still have that “old” date selected, even after you refresh everything.  Ick.  Who wants to go and update all of those slicers to point to the latest date?

I sure don’t.  So, like me, you just let them sit on an old date (or Week, Month, etc.)  This forces the consumers of that report to ALWAYS click the latest date, sometimes after scrolling the slicer to the bottom.  Every time they open the report.  They.  Don’t.  Like.  That.  And neither would you.

Read the rest of this entry »

Cumulative Interest or Inflation, Multiplying Every Value in a Column, Why Don’t We Have PRODUCTX?

November 4, 2013

Cumulative Inflation/Interest in Power Pivot - Who Needs PRODUCTX?

Given the First Two Columns at Left, How Do We Calculate the Two Columns at Right?
(It’s 1.14 * 1.18 * 1.08 * 1.08…  How do we do that?)

I Take No Credit for the Question Or the Answer!

So much coolness and none of it is mine.  Today I am merely a messenger.

First of all a great question came in on an old blog post (the one about Moore’s Law and Inflation):

HOW do you perform cumulative inflation calculations in Powerpivot. What seems trivial, multiplying all the values in a column together has me stuck, as there is no PRODUCT() formula. The only way i can think of doing it is through messy iterations but that can’t be it.

Awesome question Andy!  And I was stumped.  How do we multiply every number in a column together, which is what we need to do here?  I mean, if we had a PRODUCTX() function I’d know where to begin.

To the Mother Ship (Microsoft) We Go!

I think the world needs more X functions (I’m serious, that’s not a joke, although it IS a funny thing to say, so I say it often). 

So I took this opportunity to ask my former colleagues at Microsoft – can we have a PRODUCTX function in the future?  And is there a way to “fake it” in the meantime?  Spoiler alert:  the answer is yes.

Jeffrey Wang is One Smart Dude

Read the rest of this entry »

Traveling into the Future: Measuring Things that Lag

October 24, 2013

How do we compensate for "lagged" data in Power Pivot?

The Red Bars Are Accurately “Tied” to the Months in Which Those Sales Happened,
but the Blue Bars are Four Months “Late.”

Tales from Real Life

So you know, I wrote a book last year.  Being the author, I get reports on how well it’s selling.

What follows is strictly inevitable, considering the people involved and their addictive relationships with data.

Read the rest of this entry »