Today we will be walking through creating a Sales report in Power BI, using Dynamics GP data pulled via the OData service.
The report will only be using the “Account Transactions” view, so you will need to publish that view in Dynamics GP.
The following information is based on the assumption that you have already configured the Dynamics GP OData service for use with the Power BI Desktop application.
If not, then please refer to the link below for a walkthrough on the configuration.
The report design is based off the sample “Weekly Sales and Discounts” report that is included with Management Reporter.
I used the Management Reporter sample report as a guide on how to build my Power BI report for this example.
In the Management Reporter report, we see that we are pulling the “Sales” and “Sales Returns & Discounts” Account Categories.
Calculating the “Net Sales” by subtracting the “Sales Returns and Discounts” from the “Sales”.
And then also performing a calculation dividing the “Sales” by the “Sales Returns and Discounts” by the “Sales”, to display as “Percent of Sales”.
However, I don’t like the way that the sample does this calculation so we’ll be doing something different in Power BI.
For this report, we will be using the Matrix visualization in Power BI to display the data.
The Matrix visual displays as a Table, and allows us to add columns, calculations, and highlight individual rows similar to FRx or Management Reporter.
There are a couple of caveats that we need to be aware of with Power BI:  the first is that although Power BI does have the ability to perform drill downs, current releases do not support drilling when your report uses Calculated Measures.
Since we need to use Measures to perform our calculations, drill downs will not be available with this report.  Hopefully this will be added in a future release of Power BI.
The second thing to note is that there is a known issue with Power BI, where the “Show Items with no data” option does not work when using a Slicer – the items will not appear at all.
This will affect us since we will be using a Slicer in our report to select our date range.  See the second link below this video, as it explains the workaround.
Okay, let’s get started.
First, we’ll add the Matrix visual to the report by clicking the Matrix icon here.
Next, we’ll add the Account Categories to the Matrix.
Notice that the column displays as Account, underscore, Category, underscore, Number.
Normally I would just rename this in Power BI to Account Category.
However, in my tests the report does not load properly on the Dynamics GP Home Page with that, so instead let’s just make a new column using the Account_Category_Number view.
To create a new column, we simply click the “New Column” button in the Ribbon.
Name it Account Category, and make it equal to AccountTransaction (our view) and Account_Category_Number.
Then, just click the checkmark to save the column.
Now we can select our new column and remove the old one
One thing to note here is that in order to add and remove the items from the Matrix, you need to have the Matrix selected on the Canvas.  Sometimes when you’re working in Power BI you will lose focus on the visuals as you are clicking around, so keep this in mind as you’re working on your reports.
Now that we have added the Account Categories to the Matrix, since we are making a Sales report today we’ll want to only display the Sales categories.  For that, we can just select “Sales” and “Sales Returns and Discounts” from the Filters pane on the right
Okay great, so now we’re only displaying those two Account Categories.  Next, we’ll want to display the Amounts for these Account Categories.  Unfortunately with the AccountTransactions view from GP there is not a PeriodBalance available, so we’ll need to create a new Calculated Measure.
However, one of the nice things about Power BI is that it actually includes a “New Quick Measure” Wizard, that makes creating Calculated Measures a lot easier if you’re a beginner to Power BI.  We’ll be using this wizard throughout today’s report design
So for our Amounts, we want to use a Subtraction calculation.
And subtract Credit Amounts from Debit Amounts.
And now you will see that the New Quick Measure wizard made the subtraction calculation for us.  Let’s name this Measure “Amount”.
And then click the Check Mark to save the Measure.
Now let’s make sure we’re back on the Matrix, then click the checkbox next to our new Amount measure to add it.
Okay great, so now we have our Sales account categories and the amounts.  Now we are going to add another Visual called the Slicer, which will allow us to filter the data by a specific date range.
So first, click in the middle of the Canvas and then click the Slicer icon.
We want to be able to filter by a date range, so click the Slicer visual in the Canvas, then click the checkbox next to “TRX_Date” in the FIELDS.
Okay so now that we have our slicer, you’ll see that it’s displaying as TRX_Date again which isn’t what I like, so let’s create a new Date column and use that instead.
Now notice that we cannot add our new Date column to the existing Slicer, so if we un-check TRX_Date the slicer is removed and then we can add a new one and select our Date column instead.
Okay let’s move things around so that this looks better.
So now we can filter our Amounts by a date range.  Since I’m working with GP 2018, the data starts in the year 2024 in the Fabrikam test company’s sample data, so for now let’s filter the data by the first week in the year 2024.
And if we take a look at the Management Reporter report, our amounts look correct aside from the Rounding up that Management Reporter is configured for, so it looks like our “Amount” calculation is correct.
Looking good so far.  Now we need to add the Percent of Sales calculation.  As I mentioned, I don’t like the way that the sample report in Management Reporter does this calculation – dividing the Sales by the Sales Returns & Discounts.
So instead, for my purposes, I’m going to divide the “Total” by the “Sales” to give me the “Percent of Sales”.
Now since there is not a Total or Sales available in the AccountTransactions view, we’ll need to make those Measures ourselves.
First let’s make the “Sales” column by going back into the “New Quick Measure” wizard.
And this time we want to use the “Filter Value” calculation to filter the Amount column that we created, by our “Account Category” column, but only for the “Sales” Account Category.
Now we see that the “New Quick Measure” wizard did the filtering for us again, but we want this to just be called “Sales” so we’ll rename it.
So now we have our Sales measure that we can use in our calculation.
Now let’s use the same process to make a “Sales Returns and Discounts” measure.
Okay, now that we have our “Sales” and “Sales Returns and Discounts” columns, we can make a “Total” measure and subtract the “Sales Returns & Discounts” from the “Sales” to give us the total.
So we’ll use the same process as before with the “New Quick Measure” wizard.
So the Wizard gave us our calculation again, now let’s rename this measure as Total and then save it.
Notice that Power BI is already displaying its own Total.  That total amount is not from our calculation; Power BI has built-in functionality to total all of the amounts in the columns and display them - so that’s what we’re seeing as the Total here.
Now we’re finally ready to make our “Percent of Sales” Calculated Measure.
As I mentioned earlier, I don’t want to use the calculation from the sample report in Management Reporter so instead, we’re going to divide the “Sales” measure that we created, by the “Total” measure.
So we’re going back into the “New Quick Measure” wizard again and picking Division as our Calculation.
Then we’re going to select “Sales” as the Numerator and “Total” as the Denominator.
Thanks to the “New Quick Measure” wizard we have our division calculation again, so let’s rename this Measure as “Percent of Sales” and then save it.
Okay now we have our Percent of Sales measure!  Let’s add it to the Matrix by clicking on it again, then clicking the checkbox for “Percent of Sales” from the FIELDS.
And now we see our Percent of Sales measure doing the calculation for us and we’re actually done creating our Calculated Measures for our report at this point.
So, we’re at the point of the report design now where we want to change the look and feel.
You’ll see that the first thing we need to change is making our “Percent of Sales” measure to display the amounts as an actual percentage, rather than the decimal amount that we see now.  
To do that, we’re going to use the “Modeling” section in the Ribbon of Power BI.
Then we’ll click our “Percent of Sales” measure from the FIELDS list, and simply click the percent sign under the “Formatting” section.
Now we have a nice looking percentage and my Percent of Sales Calculated Measure is working the way I intended.
The next thing that I want to change is removing the “Percent of Sales” amounts from each row, as I only really want that to display in the Total row.
Now, I played around with this for a while and I couldn’t find a way to hide amounts in the individual cells of a Matrix.
That’s not to say that it’s not possible, just that I couldn’t personally find a way to do it.
So I found a suitable workaround of just changing the color of the font for the amounts, to match the white background so that they’re not actually visible on the report – that’s good enough for me.
If some of the more advanced users of Power BI know of a way to do this, feel free to reply in the comments as I’m sure the GP Community would be happy to know as well.
So that being said, here’s the workaround I found:
First, we’ll right-click on our “Percent of Sales” measure in the “Values” list under the “Visualizations” section.
And then click “Conditional Formatting” -> “Font color scales”.
This will open the Conditional Formatting window for the font color.  Conditional Formatting is a great feature of Power BI that you can use to display ranges in different colors, such as negative amounts in Red, etc.
For our purposes, we just want to display all of the amounts in our “Percent of Sales” in White to match the background of the report so that they’re not visible, so we’ll just choose White for both the Minimum and Maximum values.
And now we see that this gave me what I wanted on the Sales line where the amount is not visible, but since the Matrix visual displays the amounts in alternating colors in the rows by default, the percentage is still visible in the “Sales Returns & Discounts” row.
We can fix that by clicking on the Matrix again in the Canvas to select it, then clicking the little paint roller icon under the Visualizations menu to open the “Format” options.
Then, expand the “Matrix style” drop-down and instead of using the “Default” style, we’ll use “Minimal” instead.
There, that looks better already.
Let’s also display the amounts in U.S. Dollars.  Click the Amount Measure from the FIELDS list,
then click the Modeling tab in the Ribbon again and click the drop-down for the Dollar sign in the Formatting section:
And select “English (United States)”.
Now let’s give the report a name, and also show the company name.  For this, we will click an empty space in the Canvas, click the “Home” tab in the Ribbon and then click the “Text box” button.
Now that our Text Box appears, let’s click inside of it and name it “Sales Report”.
I like the Arial font, so I’m using that and I’m going to make the title Bold, and make it a bit bigger
Then below the title, I’m going to list the company name – Fabrikam, Inc. since I’m using the GP demo company – and make that a bit smaller and not in Bold.
Now it’s starting to look like a traditional Financial report like FRx or Management Reporter.
However, there’s one thing left here that I don’t like:  the Sales amounts displaying in Negative.
Management Reporter takes care of this problem by simply displaying the Sales amounts as Credit.
Unfortunately, things aren’t quite that easy in Power BI from what I could find, so we’ll need to modify our “Amount” Calculated Measure to correct it.
Again, there may be an easier way to do this than what I’m about to show you so if you know of a way, feel free to leave it in the comments.
We need to use an IF statement in the “Amount” Calculated measure, which is part of the Data Analysis Expressions (DAX) used by Power BI.  DAX is a very powerful and robust tool, so for now we’ll just be covering building the IF statement that we need.
We need to use an IF statement in the “Amount” Calculated measure, which is part of the Data Analysis Expressions (DAX) used by Power BI.  DAX is a very powerful and robust tool, so for now we’ll just be covering building the IF statement that we need.
Let’s break it down.  The current calculation is:  the SUM of the Debit Amounts, minus the SUM of the Credit Amounts.  However, we need to flip the sign – but only if the amount is truly negative.  So that’s where the IF statement comes into play.
So our logic would be:  IF the sum of the debit amounts, minus the sum of the credit amounts, is less than Zero (meaning they are negative), THEN subtract (flip the sign for) the results of the same calculation (the sum of the debit amounts, minus the sum of the credit amounts -
thus resulting in a positive amount), otherwise (ELSE) just run our original calculation – the sum of the Debit minus the sum of the Credit.
The resulting calculation in DAX looks like this.
Ok, that's a lot to take in, so let's break this down again.
So the logic is: IF the sum of the debit amounts, minus the sum of the credit amounts, is less than Zero,
THEN, being the comma, flip the sign, for the result of the same calculation.
So, subtract the same calculation - debit amounts minus credit amounts.
Otherwise (ELSE, which is the second comma), perform the original calculation again - the sum of the Debit minus the sum of the Credit.
And so, there we have it, we're no longer showing those in negative.
However, this has flipped the results of our percent of sales calculation, since we're flipping the sign and now it thinks that these are positive amounts.
So, for that, we just need to go back into our percent of sales calculation and flip the calculation.
So, originally being sales divided by total, will just put total here and sales here. And save that.
And there we have it – a Sales report that looks similar to what we would see in FRx or Management Reporter!  
If we switch the date range to the next week or month, we see that our Percent of Sales calculation works as intended and everything is looking like I wanted it to.
At this point, we’re ready to save and publish our new report so that our colleagues can begin to use it, and we can display it on the Home page of Dynamics GP.
So, that will be the end of today's presentation. Always feel free to leave comments or questions either in the blog or on the YouTube video itself.
Thanks for watching.
