SUMX of IF: A Perfect Blend of Simple & Sophisticated

August 26, 2014

SUMX of IF Used to Make Grand Totals Add Up in Power Pivot DAX

In This Case, Getting the Grand Total Correct for Each Row Required SUMX

It’s that time of year again…

…when my love of spreadsheets actually translates into a love of sports.  Yes, it’s Compulsive Data Crunching Disease season, AKA Fantasy Football Season.

Fantasy football is a game in which the contestants assemble “portfolios” of NFL players in the same manner that you might build a portfolio of stocks and bonds.  Then your portfolio (we call it a “team”) performs well if the real-life NFL players perform well, and poorly if not.  The one difference between this and the stock market is that no two “portfolios” can contain the same NFL player – so if I get Peyton Manning, the other contestants in my league (typically 8-12 people) cannot have him.

I’m participating in a new form of league this year, one in which the contestants get to keep some of the players from prior years.  (In most fantasy football leagues, you start each year from a clean slate).

We’re going to be picking our players this weekend at an “auction” or “draft,” and naturally, I want to scout my opponents ahead of time.  Muhaha.

So, what do my opponents need?

A valid portfolio consists of:

Read the rest of this entry »

The Many Faces of VALUES()

August 21, 2014

Guest Post by Scott at Tiny LizardMany Values

Maybe it is a sign of where I am on the Geek Scale compared to Rob, but where he considers EARLIER() to be a pretty hard function to understand, it just doesn’t bother me.  At least it seems to have just one purpose in life.

Now, the VALUES() function on the other hand… well, that’s just some messed up stuff right there!  Not only does nothing about it feel natural and intuitive to me, but it also seems to behave in completely different ways depending on how and where it is used.

Basically, every time I use it, I feel like I either got lucky, pulled a fast one, and that I’m a dirty cheater.  So, at least I got that going for me.

Let’s look at some of the various usages.

Read the rest of this entry »

Toggling Between Different Units via Slicer?

July 29, 2014

Is this possible?

Someone at Microsoft asked me this question the other day:

“Sort of like how you’ve used a slicer for conditional formatting, is it possible to use a slicer to change the custom formatting of a number?  In my use case, I want to be able to display currency as either full number ($1,500,000.00) or abbreviated ($1.5M) as the viewer wishes.  See below for an example of the desire.”

Use a Slicer to Change Number Formatting from Raw to Millions/Thousands M/K?

Can We Do This in Power Pivot?

My Answer:  No, not possible.  Wait, maybe.  Hmm.  OK, yes, mostly.

All of these thoughts flashed before my eyes:

  1. Power Pivot measures/calc fields must always have a consistent data type.  You can’t have a measure return numbers sometimes and text other times, for instance.  All “exits” from an IF or a SWITCH must have the same data type.
  2. Apparently, #1 is no longer true in SSAS Tabular, in the 2014 release.  They now support “variant” data type measures. 
  3. But no, Power Pivot still lacks that “variant” measure capability, at least for now.
  4. Whoa, hold on a second.  The desired result above does NOT use different data types!  It’s all numbers!  So we just need to change the math!
  5. Oh, ouch, not so fast.  The “M” and the “K” – I don’t know how to add those labels in a numerical data type.

So this means…  text measures!

Read the rest of this entry »

FrankenSpark! (Cube Formulas Meet Sparklines)

July 22, 2014

Cube Formulas in Power Pivot Combined with Sparklines:  AKA FrankenSpark

That’s a Single Spreadsheet Cell with a CUBEVALUE Formula AND a Sparkline in It!

I was working with a client last week when a question occurred to me:

“Can I put a Sparkline in a cell that already has a Cube Formula in it?”
”Oh cool, it worked!” (Cackles Maniacally)

-me, last week

Anyway, we were off and running at that point:

Cube Formulas in Power Pivot Combined with Sparklines and a Chart

“FrankenSpark” Used as Part of a Larger (Redacted and Obfuscated) Client Scorecard
(Yes, the Colors Still Need Some Work)

The How-To

Read the rest of this entry »

We Have a “Crush” on Verblike Reports

June 24, 2014


Even Very “Sophisticated” Reports/Dashboards are Often “Couch Potatoes” in Practice
(They Sit There and Expect You to Do All the Work)

Intro From Rob

I’ve been meaning to blog this for a long time – it is, after all, one of my absolute FAVORITE things to talk about.  Now, Scott beats me to it.  But he does such a good job below that I don’t have much to add, except a few graphics here and there, like the one above.

Besides, how can I not love a blog post that starts off talking about how right I am? Smile

Guest Post by Scott at Tiny Lizard

By nature, I am a rather skeptical person.  When I first hear an idea, I generally think it is probably wrong.  This isn’t one of my finer traits and  I am sure it drives Rob insane, since he has a rather impressive track record of being correct.  Thankfully, this blog entry is not about one of the times I assumed Rob was wrong…

It is actually the complete opposite.  It is one of those times when Rob told me something and it just instantly SUPER resonated with me and got stuck in me.   I suspect it will do the same for most of you.

Nouns and Verbs

imageIf you were fortunate enough to hear Rob speak recently at PASS Business Analytics Conference, you have already heard this idea.

It is super typical for us, as report authors, to generate The Report.  The Noun.  We hand it off with pride.  And The Report has all the information somebody could possibly need to make a decision.  All of it.   Row after Row. Column after Column.  Unfortunately, even with the fanciest conditional formatting in the world, it is not clear… how is somebody supposed to look at The Report and actually… do something?

Because, at the end of the day (and yes, with a handful of exceptions like keeping the SEC happy), it is our hope that somebody looks at our reports and uses it to make a decision.  To do something.  To Verb.

Read the rest of this entry »

Avoid Constant Refreshes in Excel 2013

June 5, 2014

Guest Post by Scott at Tiny Lizard

Power Pivot 2013 is a bit, shall we say, aggressive with its desire to keep your reports up-to-date.

Renaming a measure that isn’t even used yet?
Please wait, while I refresh your reports.

Adding a brand new measure that can’t possibly be used yet? 
Please wait, while I refresh your reports.

You tilted your head to the side?
Please wait, while I refresh your reports.

I had a customer with 16 pivot tables per sheet, and about 16 sheets.  Making edits was getting very non-fun, so I finally took the time to look into this fantastically stupendously wonderfully awesome solution to the problem.Untitled_thumb[4]

It worked ok. Nyah-Nyah New entries get added to your pivot table context menu, as seen on right.  Simply disable when you intend to make a bunch of edits at once, and use the extra time to read Rob’s new book.

Highly recommended for those annoyed by this in 2013.

Week Ending Date Calculation

April 29, 2014

Guess Post by Scott Senkeresty at Tiny Lizard

imageJust a quick and practical tip today.

We have a really typical looking Date table.  However, we are going to be drawing some pretty charts summarized by weeks, and our business defines “end of week” at Saturday.  So, we need a new column in our Date table that stores this “Week Ending” date for each row.

The first thought to occur to me was “well, for each Year&WeekOfYear, I just want to grab the max date”.   That sounded easy enough… EARLIER() no longer scares me…
Read the rest of this entry »

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


Diagram view is good…
But Data Dictionary is better! Make use of them both

Download files used in this post:

PowerPivot Model (xlsx) – Sample model that we would attempt to document
Dynamic Management View Queries (txt) -  DMV Queries used
Data Dictionary (xlsx) – End result of our work, data dictionary which catalogs the tables, measures and attributes of our Power Pivot Model

We would use Dynamic Management Views (DMV) to pull the schema information from our models, especially the Description field. Have you been using the Description field? Now would be a good time to start.

In Power Pivot, you can right click any element (Table, Column or Measure) and click ‘Description’
This would allow you to enter the Description for that element

Why this is better:

* Reuses the ‘Description’ field that you have hopefully already been using or can easily start using as you are building your models

* Allows you to quickly scan or search through the Dictionary. In Power View you can see the description as you hover over elements, but that is a lot of hovering if you are trying to study the model! Also the hover over descriptions are not available for Excel 2013 Power Pivot models (seems like a step backwards). Another scenario, where hover over descriptions are unavailable, is when you are hosting the Tabular Model on an SSAS server and your users are connecting via Excel.

* Documents your model for use within your BI team (even if it’s just one person, you Smile) and for use by the users who connect to the BI model

* Publish the Data Dictionary along with screenshot of Model Diagram View and your users have the best of both worlds

We will show you how you can do this for (click to jump to the section):-

Create Data Dictionary: SSAS Tabular Model

Follow the steps below, if your model is already hosted on SSAS or you have it in Excel but have access to a SSAS Server.

1. If you happen to have your model in Excel, but have access to an SSAS Server, you can “restore” an Excel PowerPivot Model into SSAS quite easily. Just right click on your server in SQL Server Management Studio and select ‘Restore from PowerPivot…’ then select your Excel Power Pivot file and step through the options.


2. Open a new Excel file, open the PowerPivot window and create and import the required DMV queries one by one, connecting to your SSAS Server. (See DMV Queries.txt)

i. Perspectives (read more about Perspectives)

ii. Dimensions

iii. Measures

iv. Attributes

3. In Power Pivot Create relationships from…
[Dimensions], [Measures], [Attributes] ==to==> [Perspective] table
…so I can easily filter on elements for only one Perspective

4. I also created a simple VISIBLE = TRUE, FALSE dimension (I just copy pasted the data into PowerPivot) and create relationships from…
[Dimensions], [Measures], [Attributes] ==to==> [Visible] table
…so that you can easily filter to only see elements that are actually visible to the end user.

Your data dictionary model you should look like this:-

5. Add simple measure to [Dimension], [Measure] and [Attribute] tables so the [Perspective] and [Visible] filters take effect. Simple RowCount does the trick.

dimensionCount := CountRows(‘Dimension’)

To get fancy I created a measure using FirstNonBlank to return MeasureDescription (see Rob’s blog post demonstrating FirstNonBlank)


Your data dictionary is ready to use!

Bonus Points: I will not be covering cover this in the article but if you wanted to, you could pull Dimensions, Attributes, Measures all in a single Table and display them more elegantly. I could not figure out how to do a UNION in a DMV query but you can certainly transfer the tables to SQL and then write a SQL query with UNION clause.

Create Data Dictionary: Excel Power Pivot Model

The preferred approach is to import (Restore from PowerPivot) your Excel Power Pivot model in SSAS (previous section shows you how) and follow the steps in the SSAS Tabular section. If that is not an option, read on.

All steps remain the same except the first part of how to query the Excel+PowerPivot model; which I cover in the steps below. But after that point, follow the same steps as for SSAS in previous section.

1. Install DAX Studio (
Some notes on usage: Worked fine with Excel 2010, read notes on the Download page about Excel 2013. I did not try it with Excel 2013. Thanks to the team who created the tool, including Marco Russo and Darren Gosbell.

2. Open your Excel Power Pivot model (the one you are attempting to document) and then from the Add-Ins tab on the ribbon, open DAX Studio

3. DAX Studio should automatically connect to the Power Pivot model in your workbook. If that does not happen and you see the error message below, read this discussion. I had to make sure I had a measure defined and that I created a Pivot Table in an Excel sheet using that measure. After that it connected to my Excel Power Pivot model without any issues.

If you get an error as below try the steps specified above


4. In the DAX Studio window, if needed change Perspective to Model and Output to New Query Sheet.

DAX Studio happily connected to the Power Pivot model (yes, it says Microsoft_SQLServer_Analysis but it is in fact connected to the Excel Power Pivot model). Change the elements shown by arrow.

5. Execute the queries (See DMV Queries.txt)  one by one, the results would be output to a new excel sheet

6. Open a new Excel file and copy paste the output to this new file and then add these to your Power Pivot model (Power Pivot > Create Linked Table). You should end up with four linked tables in Power Pivot: Perspectives, Dimensions, Measures, Attributes.

7. Now follow the SSAS steps in the previous section starting with step 3.

As you can see this is slightly tricky to do in Excel, but well worth the effort. Share the newly minted Data Dictionary along with a screenshot of the Model Diagram View with your team and users.

Disclaimer: The opinions and views expressed in this post are those of the author and do not necessarily state or reflect those of Microsoft