skip to Main Content

power pivot to power bi

A Power BI Technique Mined from the Power Pivot Archives

Below, you will find one of our all-time favorite Power BI techniques. In the ten (yes ten) years we’ve been operating this site, we’ve written over 1,000 articles on Power BI.  Hard to imagine - even for us - but true.

Years ago, we first wrote up this technique in the context of Power Pivot – Power BI’s cousin and predecessor, which we like to call “Power BI in Excel.” 

Since the two products share the same brains (DAX and M), this technique is more relevant today than ever. Everything below is 100% legit for Power BI – the same data model “shape,” the same DAX, etc. – but now you can use it to power up your Power BI visuals, and not just PivotTables.  Enjoy! Smile


By Avi Singh [Twitter]

Folks, May 21st is being celebrated as Red Nose Day across USA. Goal is to have some fun and raise money for children living in poverty. We at PowerPivotPro mix a bit of black humor in today’s post and promise to donate a portion of the proceeds from our upcoming PowerPivot Online Class (Jun 8-9) to Red Nose Day.

For today’s special, we were inspired by the CDC released graph showing distinctive causes of death by state. Not content to stare at a picture, we rebuilt the model from scratch using Power Query, Power Pivot and Power Map. Which lets us slice and dice and visualize the data various ways to our heart’s content.

Our results match closely with that of CDC, slight differences may exist since we use 10 year span from 2004-2013, whereas CDC researchers reportedly used 2001-2010.


Power Map Rendering of Distinctive Cause of Death by State

Distinctive cause of death


Distinctive Cause of Death by State (Click to see full list)

In the state of Alaska, there were 218 deaths (2004-2013) due to ‘Plane or Boat Accident’ (Listed officially as ‘Water, air and space, and other and unspecified transport accidents and their sequelae’).

Alaska’s 2013 population is listed as 735,132. Thus a crude rate of mortality due to plane or boat accidents, per 100,000 of population is

( 218 / 735,132 ) * 100,000 = 29.7

The national rate for the same happens to be just 4.9. Thus Alaska’s rate is about 6 times higher! In fact, if we look at all the listed causes, ‘Plane or Boat Accidents’ has the largest ratio when compared to the national rate. Thus for Alaska we flag, plane or boat accidents as a distinctive cause of death.

If we do the same for each of the 50 states, there are 24 distinctive causes of death (some apply to more than one state).

Data Journey: Power Query > Power Pivot > Visualization

In today’s post, I will give you a tour of the end results and make available, the underlying file. I reserve the right, to come back in a later post to actually explain the steps that I took. Hopefully on a day when I’m not working so late in the night (it’s 2:41 AM and I am still writing…sigh!).

Rough overall steps were:-

– Download data from the CDC website and parse using Power Query

– Model data using Power Pivot, creating relationships and measures

– Visualize the data in Excel, Power View, PowerBI.com, and Power Map
Yes, why not. Once you build the model correctly, building visualizations is nearly free. What an amazing state of things…I feel spoiled rotten.

“Imagine a world where cost of creating new visualizations is near zero!”


Power Pivot data model can drive any visualization tool

I went one step further, than the CDC released graph and attempted to group together the distinctive causes identified for each state. You have seen the high level image above, let me provide a few breakdowns.

Is Texas really the Tequila state?

Nephrotic Syndrome (Kidney Related) is the distinctive cause in Texas, and it is Alcoholic Liver Disease for neighboring New Mexico.

Achoo! Bless you Midwest

Influenza is the distinctive cause across Midwest states of South Dakota, Minnesota, Iowa and Wisconsin. For neighboring Michigan and Ohio it is Bronchitis and Respiratory Infection.

Should South Lock Away Their Guns?

Discharge of Firearms (accidental or otherwise) is the distinctive cause across many southern states and New Mexico. For Nevada, it is Legal Intervention.

America’s Black Eye: Coal Mining States

For the coal mining states: Kentucky, West Virginia and Pennsylvania, Pneumoconioses is the distinctive cause, a lung disease caused often by inhalation of dust in mines. In fact for West Virginia mortality rate for Pneumoconioses is 17x higher than the national rate!

Alaska: The price of beauty

Alaska, Idaho and Montana have Plane and Boat Accidents as the top distinctive cause.

Vermont: Falling for the Fall Foliage

I have been to Vermont for it’s awesome fall foliage. Speaking of fall, turns out Falls are also the distinctive cause for Vermont with about 2.5 times higher mortality rate than the nation. Is it the distracted tourists?

Happy Red Nose Day to all! Signup for our class and we’ll donate to the Red Nose cause.

[Download File]

Power On!
-Avi

Avi Singh

Avi Singh has personally experienced the transformation and empowerment that Power BI can bring - going from an Excel user to building large scale Power BI solutions. His mission now is to share the knowledge about Power Pivot and Power BI.

This Post Has 4 Comments
  1. So many of the outsized results are just environmental hazards. Where are the coal mines? Hmm, PA, WV, KY is where they are concentrated. Aviation accidents? Oh, large states with sparse populations and minimal highway density. Firearms? Oh, big hunting states. how about we toss the outliers and look at the top ten causes of death and how those differ state to state? Oh, look! When we take the data into our own hands we can do things like that for ourselves instead of just consuming the sensationalized headlines from media outlets. Go PowerBI!

  2. Avi:

    Couple of questions on your data model.

    Noticed you have a disconnected table for formulas/measures. Any insight to having it standalone vs. having the measures in the Mortality table? Is this in the event you have to load/unload this query and to avoid having to re-write the measures afterwards?

    Also noticed a number of calculated columns vs. measures. Rationale behind these?

    All may be personal preference (or the hour you were building the model) but as I’m learning Power Pivot, I’m trying to also to understand the benefits of various approaches.

    Thanks for the post.

    1. 1) Disconnected table for formulas/measures: Power Query can famously break so that you would need to delete and recreate that table in Power Pivot. There are some reported workarounds (such as “I Modified an Existing Table in Power Query and Now it Won’t Refresh”– A Fix), however I can’t get myself to trust them completely. So I put my measures in a separate table (it does not effect one bit on how they work, just where they are housed). This way when Power Query flakes out, I am okay with deleting the table from Power Pivot and recreating it again. There are some settings you would need to do (relationships, sort by…). However this saves me from writing all my measures from scratch.

      2) Calculated Column vs Measures: Go with Measures – as much as you can. Overreliance on Calculated Columns is #1 folly as per Matt Allington 5 common mistakes made by self taught DAX students.

      However for this article, I needed to create Calculated Columns, since Power Map would not play ball with measures…grrrr!! In a few cases, Power Map would just not let me drag and show a Measure, only a Calculated Column. So I relented and used Calculated Columns.

Leave a Comment or Question