Check Out Josh’s Blog on Creating Percentile in DAX

I would like to introduce a “new” blogger to you. Joshuha Owen has restarted his blog and will be covering topics on business intelligence and data. I have worked with Josh for years at a Magenic and now Pragmatic Works. I look forward to seeing what he will be writing about in the future as well on Bits, Bytes, and Words.

Here is his most current post. Enjoy!

Replicating Excel Percentile in DAX

Currently, DAX has no native percentile function so if you want to replicate a version that matches what the Excel Percentile.INC (inclusive) function does you have to jump through a few hoops. This will involve having to create several measures to hold some intermediate values to apply a final formula. In theory you could do it all in one DAX expression but it would very difficult to read and test.

Continue reading…

Advertisements

Excel BI Tip #20: Wingdings–an Excel Services Supported Indicator Alternative

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013 and later.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Wingdings? Really? How did we get here?

As you have seen from previous tips, I have been working with customers to build dashboards using Excel 2013 in SharePoint 2013. I am a big fan of conditional formatting. However, one of my customers wanted to use a specific design which used triangles as images on their dashboard to indicate whether the trend was improving or worsening. What you may not know is that drawing shapes and textboxes are among the objects not supported in Excel Services.

Here is what it looks like in Excel:

image

Here is what it looks like in Excel Services – note the warning bar at the top:

image

This led me to my first option – use indicators in conditional formatting, it has a similar image.

image

As you can see it starts out fairly small, not the large shape we want to display. So, we added the indicator into a merged cell and increased the font size. You can see the image is pixelated.

image

Making matters worse, when you upload it to Excel Services it will not honor the font size.

image

Winging it with Wingdings

For some reason, yet unknown, it occurred to me to use Wingdings. Wingdings are TrueType fonts which make them “scalable” because you can specify the font size. In this case we are looking for an upward facing triangle and down facing triangle. So the first thing we needed to do was try to find out if those symbols existed. Here is one of the clearest cheatsheets I found for Wingdings font set: http://speakingppt.com/2011/10/31/finally-a-printable-character-map-of-the-wingdings-fonts/. Bruce has created a PowerPoint slide which is easy to follow. Whether you use, his or look it up on your own, you will find that in Windings 3, the letters “p” and “q” are the directional triangles that we need (p and q). Now let’s build our visualization with Wingdings (and no, I can’t believe I said that as well).

The key to using wingdings is that you need place the font representing what you are looking for in the field as shown here. You can see that the value in the cell is “p” but the wingding font gives us the triangle.

image

Not only can you affect size, you can change the color.

image

But the end goal was to have this work in Excel Services. So I can save this sheet to my Office 365 SharePoint site. As you can see here, it works as desired.

image

Here is how you can put this to practical use. Let’s say we want to use a green smiley (Wingdings – J) and a red frown (Wingdings – L) based on our value. Greater than or equal to .5 or 50% will be smiling and less than .5 will be frowning. Assuming that the value we are evaluating is in K5, we would use the following formula to set the value:

=IF(K5>=0.5, “J”, “L”)

This sets the text value that we want to use. Because we are using a character value in the field, we can use conditional formatting to set the appropriate color by using the Highlight Cells Rules – Text That Contains… option. You will create two rules, one for J and one for L. You can use a default setting or create a custom format to change the color.

image image

By using Wingdings and Webdings, you will be able to further enhance your dashboards with a variety of symbols. I hope you have fun with your dashboards and get to tell your users or designers that, yes, you do use Wingdings! Enjoy winging it!

Note: The target environment needs to support the Wingding fonts. We have seen this not work when using iPads for instance. Be sure to consider and test your target environments for this solution.

Excel BI Tip #19: Using the TEXT Function

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013 and later.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Formatting In Cells with the TEXT Function

This is a shorter tip. Sometimes the data you use in a cell needs to be formatted. This is particularly helpful when coupled with the CUBEVALUE function from the previous tip. The TEXT function allows you to format values to the pattern you want. Here are some of examples of using the TEXT function.

  • Number formatting – 1000 to 1,000
    • =TEXT(1000,”#,#”)
  • Number formatting with a decimal – 10.1 to 10.10
    • =TEXT(10.1,”#,#.00″)
  • Date formatting – 10/1/2014 to 2014-Oct
    • =TEXT(“10/01/2014″,”YYYY-MMM”)

As you can see, the standard Microsoft formatting can be applied to values. Here is what it would look like when used with the CUBEVALUE function.

=TEXT(CUBEVALUE(“ThisWorkbookDataModel”,$B$4,$B8,G$5,Slicer_Poll_Has_Submissions), “#,#.0#”)

This function will not work in the pivot tables themselves because cell formulas cannot be created in pivot tables. However, when you decompose a pivot table, you can use the CUBEVALUE and CUBEMEMBER functions as values to be formatted.

One caveat is that the TEXT function returns the value as a string or text data type in Excel. That means this function is best used to create headers and text with values in it. If you wanted to create a label plus a value in a field for a list of various metrics, you can concatenate the text with the resulting TEXT function.

=”Average Poll Count for Men: ” & TEXT(CUBEVALUE(“ThisWorkbookDataModel”,$B$4,$B8,G$5,Slicer_Poll_Has_Submissions), “#,#.0#”)

This would return a value such as “Average Poll Count for Men: 8.0” in the cell.

While a simple tip, it can add significantly to the look and feel of your Excel based dashboards.