Creating Sales Invoice Reports using Power BI is a straightforward concept, but it comes with some quirks when you actually go to build the report and evaluate the results. I’ll build a visual report in this post using the Filled Map control, but when I add slicers to the canvas, I see way too many options; I end-up seeing slicers that apply to customers who don’t owe any money. To fix that problem, I’ll use a Report Level Filter to ensure I only include data records for customers with open sales invoices.

Side note: I’m using the Adventure Works database hosted in a Microsoft Azure SQL Database. You can read about getting all of that setup, and using Excel with Power Pivot in this post.

Add the Filled Map and Slicer Controls

I’ll start by adding the Filled Map and Slicer Controls without the Report Level Filter to demonstrate the problem. In reality, I’d add the Report Level Filter first because I already know my constraints. But, setting the stage, I place a Filled Map on the Power BI Desktop canvas, and add “StateProvince” to the Location field for that control. Then, navigate to the  Format Pane for the map control, choose Data colors, and then Advanced controls. Here’s where I’ll specify to format my color scale based on the Sum of Total due.

So far, everything is looking fine, but now I’ll specify “StateProvince” as the Slicer Control data field, and the result is a slicer populated with every single State/Province in the database…even though nobody owes any payments from a great majority of these locations. The Slicer also has the “(Blank)” option, and the solution I’ll implement as a Report Level Filter also takes care of removing Blank from the Slicer. You can see in this image that there are dozens of options – too many of which will have no data (i.e. no invoice amount is due):

One final note on why the Report Level Filter is important. It may not seem obvious with only the map control, but when I add a Pie Chart or Bar Chart in Power BI later, they’d be blank if I selected a state with no amount due, and that doesn’t make sense to a user…having a visual be empty / blank and basically just disappearing from the canvas. That looks like a broken report.

Here’s a demo…first I’ll select California, which has some past due amounts. Then Colorado…which also has some amount due. But then I select Illinois and the chart disappears. So, I get a little uncertain and check another state…nothing. Then another…nothing. Meanwhile, the map is highlighting these states, making it look like something should appear in the bar chart, but it doesn’t.

Now you see that it does not make sense to include zero-value sales invoices in a report which is meant to show total amount due by customer.

Add the Report Level Filter

All that hype for a fairly simple, but very significant update to the report.

• Locate Report level filters in the Visualizations pane.
• Drag Sum Total Due into the Drag data fields here section.
• Specify the filter to only show values that are greater than 0.

Now we see behavior that users expect, and I added a Pie Chart in Power BI to show amount due by state too:

Thanks for reading.

Categories: Power BI

Tags: Power BI