Month: August 2015

Power BI Tip: Making similar sized KPI Boxes / Charts

Power BI Tip: Making similar sized KPI Boxes / Charts

August 20, 2015

Recently, I got asked by one of my readers if there is an easier way to make similar sized KPI boxes or charts in Power BI, other than manually resizing each individual visualization. As you know, making similar sized KPI boxes and / or charts are a design technique to make your reports symmetric and more aesthetically pleasing. Currently Power BI does not offer us a way to key in the width / height of the visualizations and it might seem like manual resizing is the only option. This tip is a much more simpler and precise way to do the same.


I) Making Similar Sized Charts

Making similar sized charts are easy. For that, click on the completed chart that you want to copy and then press CTRL + C on the keyboard to copy the chart. After that, press CTRL + V to paste the chart.

1 Copy paste chart

Now, you can go ahead and change the dimensions and measures of the second (and also the chart time), but one at a time. You should have a same sized different chart now.

2 Changing chart

The reason why I said to do it one at a time is because you will lose the chart if you remove all the dimensions and measures.

3 Losing hcart when you remove all dimensions and measures

II) Making Similar Sized KPI Boxes

Now this is a little bit more trickier. Let’s say I use the same CTRL + C, CTRL + V to copy paste the textbox.

4 Copy paste textbox

Now when I try to change the measure, notice how I can’t just replace the existing measure with the new one. I tried putting it on the card as well as on the Fields section. And when I try to remove the measure from the Fields, the entire card disappears.

5 Trying to replace measure

To get this to work, first change the chart type to something else, say a pie chart. Then drag and drop the new measure into the Values section. Make sure to remove the old measure and then revert back to the card visualization. Voila, now you have two KPI boxes of the same size.

6 Duplicating card by changing chart type

Now that you know this trick, go forth and make some pretty dashboards in Power BI!

Power BI Dashboard

You can also make some cool indicators on your KPI box with this trick that I showed in a previous post.

Note : Created using Power BI Desktop version listed below


Posted by SQLJason, 0 comments
Using DAX to make your Power BI DataViz more meaningful

Using DAX to make your Power BI DataViz more meaningful

August 13, 2015

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.

1 Line Chart

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

2 Power BI Sparkline

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

3 Bar chart for Promotions

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))

4 Add new measure

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

5 New Sparkline

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.

6 Low High

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.

7 Waterfall Chart Power BI

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

Demo Day: Analyzing the Effectiveness of Promotions in Retail

Posted by SQLJason, 7 comments