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

image

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

image

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.

NetPromoter.com describes NPS as:

Read the rest of this entry »


TopN as viewed by DAX Table Queries

March 13, 2014

 
Guest post by Scott Senkeresty

image

Get it?  “Median?”  SO Funny!

Intro

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 www.linkedin.com/in/avichalsingh

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…
image
But Data Dictionary is better! Make use of them both
image

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’
image
This would allow you to enter the Description for that element
clip_image003

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.

clip_image004

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:-
clip_image005

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)

DescriptionDimension:=FirstNonBlank(‘Dimension’[Description]
,IF([dimensionCount]>=1,1,Blank())
)

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 (daxstudio.codeplex.com)
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
clip_image006

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
clip_image007

 

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.
clip_image008

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


“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 »