We recently discussed at work how to display the contribution of multiple firms to the total revenue in Power BI, in a way that the firms can be filtered but the total revenue stays constant. We came up with a very simple no-code solution.
Data from Dynamics AX 2012 R3
All the required data can be fetched from the CustInvoiceJour table in Dynamics AX. There are three datasets in the Power BI data model. First, the invoices including the InvoiceAmountMST, InvoiceYear and DataAreaId. Second the sum of InvoiceAmountMST grouped by Year and DataAreaId. Finally, a table that contains the years. Here are the SQL statements to load the data.
select
InvoiceId, InvoiceAmountMST, InvoiceDate, DataAreaId,
year(InvoiceDate) as InvoiceYear
from CustInvoiceJour
Total Revenue:
select
sum(InvoiceAmountMST) as InvoiceAmountTotal,
year(InvoiceDate) as InvoiceYear, DATAAREAID
from CustInvoiceJour
group by year(InvoiceDate),DATAAREAID
order by DATAAREAID
In Power BI create a new dataset and enter the years manually, like 2010, 2011, 2012, etc.
In Power BI go to the data model an connect the Invoices and Total Revenue with the dataset that contains the years
Line and stacked Column
Add a new Line and stacked Column chart to the Power BI Report. Set the field Year from the Year table as shared axis. From the CustInvoiceJour set the DataAreaId as Colum Series and the InvoiceAmountMST as Column values. Set the InvoiceAmountTotal from the Totals table as line values. In the format tab disable the Y-Axis property Show Secondary.
Your diagram should look like this. Filtering the companies does not effect the total revenue line.
