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 »


Has Power Pivot Changed Your Life? I Want to Know!

August 19, 2014

This face obviously represents happy.

We Want to Hear Your Cool Stories!

Unprompted Confirmation

In last week’s post I explained why I now “kick off” my training sessions with the bold promise of “Power Pivot will change not only your career, but also your LIFE.”  (Please go back and read that, briefly, if you have not – because this post won’t make as much sense otherwise).

Well, a funny thing has been happening lately – I’ve been getting emails from people saying that it HAS changed their life.  Literally in those words.

Are these people just being nice to me, confirming my bold statement?

Nope, because none of them has heard me say it before – these are people I have never met in person!

In fact, this pattern of emails was the reason why I got started writing last week’s post.  I actually intended to JUST write THIS post, the “has it changed your life” post, but along the way I realized I needed to add the background, so this got split into part two.

Please Share Your Stories!

If you are feeling like this, please leave a commentIf you’ve experienced life change as a result of Power Pivot, please leave a comment at the end of this post!

Or if you’re a bit shy about that, drop me an email.  My address isn’t hard to find or guess.

Take it Away, Rob’s Inbox!

imageThese are real emails from real readers, slightly edited to maintain their privacy.  In all cases, I am bolding the “life changing” portion of the quote:

Read the rest of this entry »


Power Pivot: a revolution in slow motion

August 14, 2014

By Avichal Singh

The first time you truly experience Power Pivot – not a demo, not some random public data set or someone else’s data – your own business data all lit up using Power Pivot. It is a transformative experience. And you know there is no turning back.

I remember that moment, I remember thinking this is HUGE, this gets BI in the hands of people who really need it. Power Pivot is surely is going to explode.

Sadly, it does not feel like that is happening :-(

As I have become more involved with the Modern Excel User Group (also on LinkedIn), I have realized that there are way too many people in buckets a) and b) below and not nearly enough in c).

a) Sad smile Unaware. Have not heard of Power Pivot or Power BI
b) Disappointed smile Aware but have not truly experienced the capabilities of Power Pivot
c) Red heart In Love with Power Pivot

Note: Are there stages between b) and c)? Between experiencing Power Pivot and falling in love? Perhaps. But guided by the right hands that path should be really short :-)

Unaware: Don’t know what they are missing

For the unaware group, I do my part; talking to anyone who would lend me an ear, about Power Pivot and Power BI at any event or gathering. But overall we would need to trust the higher powers :-) within Microsoft with that task. However I will make the offerings below to the powers that be:

- Power Pivot should be a free add-in for all versions of Excel 2013, just the way it is for Excel 2010. I feel it even makes good business sense for Microsoft. Power Pivot has the potential to attract a large user base and would solidly anchor Office and Office 365 in the rapidly changing world. These users can then be up sold services like Power BI.

- Power Pivot add-in should be easily discoverable. Both Power View and Power Map have buttons as part of the standard Excel ribbon, which can be used to easily activate these tools. Not so for Power Pivot.
It takes 8 mouse clicks to activate the add-in so you can launch Power Pivot.
File > Options > Add-Ins > Click Dropdown > COM Add-Ins > Go > Select ‘Microsoft Office Power Pivot for Excel 2013’ > Ok
As the first born in the Power BI suite, it is time for Power Pivot to claim it’s rightful place on the standard Excel ribbon and it would fit perfectly under the Data tab.

Currently in Excel 2013 Desired in Excel 2013
Currently in Excel 2013: Powe Map, Power View Desired in Excel 2013: Power Pivot or Data Model

Note: There was a shift in Excel 2013 to weave in Power Pivot so closely with the tool (and call it Data Model) that potentially many users would not even need to learn or use the Power Pivot window. IMHO, that is just keeping people away from all the goodness and richness of Power Pivot. Let them come, let them explore and experience.

Aware but not in Love: Mind the Gap!

While I may feel helpless in making a dent in the Unaware group, I feel impassioned and even obligated to help the Aware but inexperienced group.

I have spoken to many Excel users and noticed that there is a gap between knowing about Power Pivot and actually trying it out yourself to experience its true power. There is a step there that many users simply cannot take. Being on the other side, it baffles me a bit. I feel like yelling “Hey, come on over, the grass IS greener on the other side!”

From Excel to Power Pivot: Mind the gap!

Read the rest of this entry »


Vending Machine or Kitchen: Two Kinds of Self-Service

August 12, 2014

Preface:  Breaking the Consulting “Rulebook”

Power Pivot Allows Us to Set a Much Higher Bar - and Then Clear It

This may seem bold:  Starting about six months ago, whenever I find myself in a room with spreadsheet/business/database people, one of the first things I tell them is that Power Pivot won’t just change their work, it will change their LIVES.  That’s right – I tell them, with a straight face, that this business technology will actually make them happier.

That violates one of the unwritten rules of consulting, which is to underpromise and overdeliver – set expectations low enough that no one ever has reason to complain or be disappointed.

I understand why it’s traditionally a good practice to “set the bar low.”  I get it.  I truly do.

“Consumer with Choices” vs. “Empowered Producer” is a BIG Difference

But Power Pivot is different.  Yes, in the truly breakthrough, transformational sense – you knew I was going to say that.  But different in another critical sense as well – it requires (and incents!) the analyst types on the Business side to participate to a degree unlike with any other BI tool.

No matter how they are marketed, every other BI tool I have ever seen treats the business user as a glorified Consumer.  Yes, Business Objects and Cognos for sure, but I’m also looking at you, Tableau and Qlikview and Spotfire.

Read the rest of this entry »


Filters CAN Flow Up Hill – Via Formulas That Is

August 7, 2014

By Matt Allington

Intro from Rob

imageMany of you already know of Matt Allington and his background as BI Director for Coca-Cola Asia Pacific.  Matt recently flew around the world (literally) to attend my training course with the purpose of becoming accredited by me as the official PowerPivotPro University Trainer for Australia (we even have a picture to prove it – displayed at right).

Now that Matt has my approval to teach my material, I have invited Matt to be a regular blogger on PowerPivotPro.com.  This is Matt’s first post in this capacity, so over to you Matt.

Confessions of a DAX student

Matt here:  I want to share with you a simple mistake I made early on in my DAX journey, and also create awareness of how easy it is to fall into a similar trap. This post will explain the mistake and provide the solution to how you can get filters to flow up hill – via formulae that is.

If you learnt DAX the Rob Collie way (like I did) you would be very familiar with Rob’s best practice of placing the lookup tables above the data tables in the PowerPivot Diagram View.

schema

The reason Rob teaches it this way is because it is very easy for the reader to visualise the flow of a filtered table – filters flow down hill.

Read the rest of this entry »


A Neat Trick/Macro for More Readable Pivots

August 5, 2014

 
OK, you’ve been a good Power Pivot author and given your measures clearly descriptive names.

Your punishment is spending all day looking at pivots like this:

Your Pivot is Too Wide for the Screen

Hey, Where’s the Rest of My Information?
(Hint:  It’s in “Scrollsville.”)

Nice and Cleanly Readable Pivot

MUCH Better:  Last Two Measures Completely Visible, With Space to Spare!
(Assuming Vertical Space Isn’t a Problem, Of Course)

A Trick I “Harvested” From a Client

Awhile back I was working with a gentleman named Tom Phelan who repeatedly used a series of click mouse clicks to achieve the sort of layout pictured above.  After seeing him do that about ten times I asked him to slow down so I could see what the clicks were.

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 »


Next Week’s Sold Out Class: One Spot Re-Opens

July 25, 2014

 
Hi folks.  Next week’s 2-day class (Wed-Thu, 7/30-7/31) taught by me (Rob) has been sold out for awhile now, but we had a last minute cancelation.

So if you live in the Cleveland area, or feel like some last-minute travel, drop us a note at empower@powerpivotpro.com

If you ARE considering the trip, you won’t be the only one traveling in, btw.  Of next week’s class, 16 of 17 students are coming from out of town.  Maybe you want to be lucky number 17 of 18.

We’re also doing a social outing/dinner for the class on Wednesday night.  The famous Matt Allington will be joining us from Down Under.

Note that we’re doing another class in August as well, so if you’re not inclined to join us on short notice, there’s another option as well.


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 »


Adding tables to a Power Pivot model from VBA (in Excel 2013)

July 17, 2014

Guest Post by Dany Hoter

After I published a post about manipulating relationships, Rob suggested that I take a step back and cover the entire scope of what’s possible with the object model.

Can you build a model from scratch? Can you add a new table to an existing table? Can you add calculated columns? What about calculated measures? , Can you change a connection for an existing table in the model?

The short answer to these questions is Yes, Yes, No, No, Yes

The longer version is the rest of this post. Everything in this post is NOT possible in Excel 2010 – this stuff works in 2013 only.

The object model consists of the following elements:

clip_image001

The only property that I found useful in this list is ModelRelationships collection which I used extensively in the previous post.

The ModelTables collection looks promising as it contains ModelTableColumns and could be the way to introduce new tables, new columns or even measures into the model. Unfortunately all these collections are read-only and cannot be used for adding to the model.

So how is still possible to add new tables or even to start a model from scratch?

It all has to do with the method add2 of the Connections collection.

Read the rest of this entry »


The Simple Art of Being Prepared

July 15, 2014

Saluting a Mentor

image

Heikki Kanerva, Right.
(At left is Juha Niemisto, a Programmer from the Excel Team and Mutual Friend)

Taking a bit of a break today from “technical” posts and doing that whole “professional observations slash storytelling” thing.

My first real mentor at Microsoft was a guy named Heikki Kanerva.  I learned many, many things from that man, but the way in which he cared about his people, and PROTECTED his people, will always eclipse even those things.  I wouldn’t have had the time or the space to learn had I not lived in the Heikki-created “safe zone” for those crucial early years of 1997-1998.

And the charisma.  The leadership.  You know those fantasy stories about barbarian clans in the frozen wastelands of the North, where the clans have perpetually been at war with each other, but now a New and Evil Threat looms from the South, and the clans must set aside their pride and biases and band together, or face certain extermination?

In those stories, there’s always a charismatic leader who emerges to unite said clans, the Strong Leader who possesses brains, brawn, and charisma in just the right proportions.

And Heikki was pretty much the real-life embodiment of that character, in every way.  He played that role every day for the entire Office organization, and I have never seen anything like his powers since.

A Mentor’s Parting Lesson

Years later, when I no longer worked for Heikki, he passed away suddenly under tragic circumstances – a story that I may someday tell here, but not the focus for today.

But leave it to Heikki to still manage to teach AFTER departing this world.

Read the rest of this entry »