Using DAX to make your Power BI DataViz more meaningful

Share this...

Twitter

Linkedin

Facebook

Pinterest

Reddit

One of the best features I like about Power BI Desktop is that the data acquisition, data modeling and data visualization functionalities are all integrated in a user friendly way. I don’t have to leave the Power BI desktop to perform any of these common operations when I am playing with my data, unlike so many other tools. Why is this important? Because you tend to be more productive when you have everything you need in one place and also, you tend to be more creative when you have the power to model the data along with making your visualizations. DAX has some really powerful data modeling capabilities, and when you couple that with Power BI, you can start giving more meaning to your visualizations and get faster insights. I recently made a video on my company’s blog site on how to analyze Promotion Effectiveness and for the same, I was using a dashboard made in Power BI Desktop. I am just highlighting two examples of how I used DAX to make my visualizations better.

I) Make better Sparklines by Highlighting

In my Promotion Effectiveness Analytics demo, the sparklines highlight (instead of just filtering) the value for the months where the selected promotions ran. This gives us a better understanding of what is happening before, during and after the promotion. Now, this is not possible out of the box in Power BI, but with just a little bit of DAX magic, we can make this work.

1) First let us see how to build a simple sparkline in Power BI. For that, select the month & sales and make it as a line chart.

2) Next, let us remove all the format options so that it looks like a sparkline. Also feel free to resize it

3) Make a new bar chart for Sales by Promotions, so that we can use it to filter the sparkline.

You can see that the sparkline automatically gets cross-filtered to only the months where the promotion ran. This is great but what would add more value is if we could see the months highlighted instead of just filtered. That would let us know how the sales are before, during and after the promotions instead of just during the promotions.

4) Make a new measure by clicking on the dropdown next to the table, and then use the formula below

Sales Amount Total = CALCULATE([Sales Amount], ALL(Promotion))

Basically, we are making a measure which will show the Sales irrespective of whether the Promotions are filtered.

5) Now add the new measure to our sparkline (make sure to give it a lighter color like light grey for a better effect).

Now you can see that the the grey line shows the sales for all the promotions while the green line highlights just for the selected promotion. You can also use this technique in other creative ways, for e.g., to highlight the max and min points of a sparkline.

II) Waterfall charts for Measures

In my Promotion Effectiveness Analytics demo, I created a Waterfall chart to show the breakdown of Customer Visits. Basically,

Customer Visits (This Year) = Customer Visits (Last Year) – Lost Customers + New Customers

I have individual measures for each of those, and in this case, a waterfall chart would be a great way to show the breakdown. However, we can only put columns in the category axis for waterfall chart in Power BI. But with some data modeling, we can get this done.

1) Make a dummy dimension called Customer Retention with just one column and 3 values – Last Year, New and Lost. I just made the dummy table in a text file and imported it to Power BI.

Note that this is a disconnected table and will have no relations to any other table.

2) Create a new measure called Customer Visits as shown below

Customer Visits = IF(HASONEVALUE(‘Customer Retention'[Customer Retention]),
                        SWITCH(VALUES(‘Customer Retention'[Customer Retention]),
                                “New”, [New Customers],
                                “Lost”, [Lost Customers],
                                “Last Year”, [Visits (LY)]))

Basically you are assigning the appropriate measures for New, Lost and Last Year based on the values for the disconnected Customer Retention table.

3) Now just make a Waterfall chart with the Customer Retention column and Customer Visits measure to clearly see the breakdown.

Hope you got some ideas on what all we can do when we combine DAX with dataviz. Stay tuned for more as we expect to see Microsoft release more functionalities around the tool.

Note : Created using Power BI Desktop version listed below

Feel free to watch my video on analyzing promotion effectiveness by clicking on the image below