Latest Enterprise DNA Initiatives

Power BI Accelerator Week #3 is Live!

All,

Power BI Accelerator Week #3 Is Live!

OK, I think we might be on to something here. Great participation again in Week #2, and I also noticed a number of Accelerator participants jumping into the Enterprise DNA Data Challenge for the first time, which is awesome. So, some great synergy between these initiatives.

Now, on to Week #3. Each week of Accelerator focuses on a particular theme fundamental to developing a well-rounded understanding of Power BI. In Week #1, we generally explored the power of context. In Week #2, we discussed how to construct a proper data model. This week, we return to the topic of context, focused on how to change context in DAX using CALCULATE, with a particular focus on the use of ALL, ALLSELECTED, ALLEXCEPT and REMOVEFILTERS. These functions, are the fundamental building blocks that make DAX so powerful.

The scenario this week is to continue our engagement with the office supply superstore client, building the following report that does a deep dive analysis on the profitability of their business. Even using just the concepts we have covered in weeks #1 and #2, plus what we will cover this week we are able to generate a report that personally I would be pleased to provide to a client or my managers – showing once again that you do not need to be an expert in Power BI in order to generate reports that provide tremendous insight and value.

That being said, this is not an easy challenge. For most of us, these concepts take a while and some repeated practice before they fully “click”. The report is composed of four separate sub- analyses. The cumulative analysis at the top right is pretty straightforward, but they get progressively more difficult as you move counterclockwise. Even if you don’t get all four on the first try, that’s fine – give them your best shot and I guarantee whether you get to the full solution are not, the effort will result in a lot of learning and will make the solution session that much more meaningful.

A few points to note:

  • I have modified the dataset from Week #2 slightly to remove the records associated with copier sales. The profits on this particular subcategory were so high they were distorting the results in every other area. If you use your Week #2 data model rather than the one I provide below, just be sure to remove these records from your model as well.
  • Since we haven’t covered variables yet, my solution does not use them. However, if you feel comfortable using them, by all means do so – it will substantially reduce the number of measures you need to write.
  • The attached PBIX contains additional instructions, as well as reference materials if you run into trouble with any of the key concepts needed to complete this exercise. You can also always post questions on the forum in this thread (just be sure to use the spoiler blur to prevent others from seeing information they may not want to).

We will be doing our live solution event on Wednesday, August 11 at 5 PM EDT. Registration link to follow shortly.

Enjoy, and thanks for participating! Hope to see you next week at the solution session.

• Brian

P.S. Enormous thanks to Accelerator Advisory Team members @KimC and @eric_m for their outstanding input on this week’s problem.

EDNA Power BI Accelerator – Week 3 Problem Final.pbix (9.7 MB)

5 Likes

All,

A couple of additional things to mention in general about Power BI Accelerator.

While I think you will get the most out of this initiative if you participate weekly, each problem is also intended to be self-contained so even if you haven’t done the prior weeks’ problems you can jump in and participate in any week.

However, for those who complete the entire series, our star graphic designer Karen Sangil has developed an awesome series of badges to denote completion of/participation in a wide range of Enterprise DNA initiatives. Here’s the one she developed for Accelerator:

You can post it on your social media accounts, hang it on your refrigerator, and/or wear it on a chain around your neck - the choice will be yours…

  • Brian

6 Likes

This is an excellent accelerator challenge by Brian. I have learnt so much from doing it and I highly recommend it.

4 Likes

Hi @BrianJ

I hope I can make a suggestion.

Is it possible to put the Suggested Learning Resources actual links within the actual posting? This way we can actually click on the link. Resource tab within the power bi file, we can’t click on the link to take us to the actual site.

Thanks
Keith

4 Likes

@Keith ,

Thanks - excellent suggestion! I will do that as standard practice from this point forward.

Here are the Week #3 Suggested Learning Resources:

CALCULATE is the Key to Anything Advanced

A New Look at CALCULATE

Using ALL vs. ALLSELECTED for % of Totals Measure

ALL and ALLEXCEPT

Highlight Last Period within a Visual (Conditional Formatting)

3 Likes

thanks Brian :slight_smile:
I didn’t think it would take up that much space sorry…I thought just the link

1 Like

All,

FYI - because this one is a click more challenging, we’ve decided to give everyone some extra time and bumped the live solution event out a week to Wed. August 11. Registration link to follow later today.

  • Brian
2 Likes

Brian:

Are we allowed to add a column to a Lookup table?

John Giles

1 Like

@JohnG ,

Sure. No restrictions on this one - you can make any changes necessary to implement your approach.

  • Brian
1 Like

Thanks, Brian. As always, a prompt response.

John Giles

I started the Acceleration series with week 2, I had already decided to take all the new weeks and was hesitating to come back the week 1 but this will definitely encourage me to also take the first week.

2 Likes

@MehdiH ,

Great! Looking at your very impressive recent Challenge #14 entry, I expect you will breeze through Week #1, but the extra practice never hurt anyone. I do think you will find Week #3 quite interesting.

  • Brian
1 Like

Totally agree

1 Like

Thanks for another one of these!

I’m struggling a bit on the bottom 2 visualisations. I’m not sure what is meant by Baseline Share on the bottom left one? The map I think I know what I need to do, just need to work out how!

@jamie.bryan ,

If you’ve already got the top two and are into the third one, you’re off to a very good start.

Baseline Share is the relative percent breakdown by shipping mode before any slicers are applied (or another way to think of it is all slicers are reset to all ).

The map just shows states with the highest and lowest profits within the selected region(s). This one I would consider a “stretch” problem – definitely the most difficult in the group, but not unreasonably so I think, particularly if you break it down into pieces by measure branching.

One thing that I thought people might find helpful was to be able to play with my live report via Publish to Web:

Good luck!

– Brian

1 Like

@BrianJ

Ah I see, thank you that’s sorted that one.

For the map:

My train of thought at the moment is I need a measure for the highest profit state, a measure for the lowest profit state, then a measure that calculates total profits but sets everything else to 0 that isn’t the state found by those 2 measures. That would then make everything that isn’t those 2 states the same colour to create the effect seen on your report.

Bit stuck on how to do that though, or if I’m barking up the wrong tree! I’ll throw my hat into the ring with what I have for now and keep working on it/get some inspiration from any others that may be submitted to finish the map off.

EDNA Power BI Accelerator – Week 3 Problem Final.pbix (9.7 MB)

Edit:

Cracked it!

2 Likes

@jamie.bryan ,

my man

Absolutely perfect - well done, sir! Very creative approach to the min and max profit measures too.

Thanks very much for participating! Hope you found it a useful exercise.

  • Brian

Hi @BrianJ

Just to confirm the copier sales you removed as stated in your points to note above has to with the profits associated with the Xeror, Avery, and Newell products. Is that correct ?

@Eze ,

No, it looks like 46 records with subcategory equal to “Copiers” and the brands being Canon, Hewlett-Packard, Brother and Sharp.

  • Brian
1 Like

@stevens ,

Not sure why you withdrew your entry – from the look of it, you absolutely nailed all four elements. Nice work!

  • Brian