I was recently working with one of our manufacturing customer’s Regional Sales Managers who is assigned to the highest volume distributors in his territory. He was concerned about sharing data with customers, and making sure it’s only their data, while making it quick and easy to do. This customer already uses Power BI, which has many great ways to share data, but there are simplier avenues. In this case, the sales manager wanted to send a spreadsheet that only focuses on a specific distributor. So let’s take a look at how we helped our customer quickly share information with their distributors while protecting the privacy of other distributors.
As a summary, here are the key things that we needed to solve for:
- The data shared should only contain information relevant to the specific distributor. While there are several ways data can be shared through portals, shared Power BI reports, and more, nothing beats emailing an Excel spreadsheet in some situations.
- The perceived risk is that a salesperson would export the whole dataset, or data from multiple distributors, and then either forget, or not properly delete all the rows pertaining to other distributors prior to sending it externally.
- The required feature for the salespeople communicating with distributors, is that they can export only allowed data, and they can only export data for a single distributor at a time.
To investigate, we need to look at two items: How can we control the export to Excel options, and how do we ensure that a user has filtered the data by one – and only one – distributor?
Export to Excel
There are three options for exporting to Excel. These settings are managed on a per-report basis.
- Summarized data lets you export exactly what you see on the screen. If that is a table, then it includes every record and column included in that table. If it is a bar chart, column chart or another visual, then it exports the aggregate data as needed. If you let users edit the reports, they could add more fields to your tables, create different filters, etc., so they have the unwanted potential of exporting more than desired. To avoid this, users should not have to edit right to the report or dataset.
- Summarized data and underlying data will export all data under the visual with every column in the data set.
- None does not allow users to export to Excel. There’s a catch here. Even if this option is set to none, and users still have “build” access to the report and dataset, they will be able to create ad hoc reports on the full dataset in Excel and export it from there. To avoid this, make sure users only have “Read” access to the report and dataset.
In our scenario, we will set the Export to Excel settings for Summarized data, and we will create a separate report just for exporting data. This will ensure data is only exported in a controlled manner.
The other part of the equation is to ensure that a filter for a distributor – and only one distributor – has been added to the data.
The report we made for exporting data to share with distributors contains a table with the allowed columns included.
The challenge is that Power BI by default shows all data when no filters are selected. Power BI also by default let’s the users select multiple distributors which we do not want.
There’s a simple solution to this, and a slightly less simple solution to this.
We can create a report in Power BI with a table containing the data needed for export. We add a slicer on the side with a distributor selection.
With the slicer selected, make sure “Single select” is enabled on the visual.
Now a user will always only be able to select one distributor, from here using the data pertaining to one distributor only, and export the data straight from the visual.
The Option with More Filtering Capabilities
While the above meets the requirements, more flexibility can be needed in the filtering option. For example, we may need to use other visuals as a part of the filtering option.
To allow for this, we can add a measure in Power BI that determines if one filter, and only one, has been applied to the table.
The measure can look like this.
In the above scenario, the Opportunity table have had a distributor field added. This field is checked whether one filter has been applied by the HASONEFILTER DAX formula. If only filter applied, then set a value of “Allow” or none or multiple filters, then “Do NOT allow”.
This measure is then added as filter for the table visual with the data we need to export. The filter is set to only show records when “Allow” is the value.
With this setup, we can now use other visuals for filtering while still controlling the table used for export and ensure only one distributor is exported at a time.
This is one simple option to control what records are shared when salespeople export data from Power BI to send to distributors or customers. If needed, it is possible to add more security layers, including Row Level Security, ensuring that this report made for export only has records pertaining to the logged-in user or that user’s territory.
Also, as mentioned in the beginning, Power BI visuals and tables can also be shared with customers via a portal, embedded in other business applications, or by external users in Power BI directly.
If you would like to know more about how to accomplish this for your sales organization, reach out to us.