Visualize the firms contribution to total revenue in Power BI

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.

Power BI Years Table

In Power BI go to the data model an connect the Invoices and Total Revenue with the dataset that contains the years

image

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.

Diagram settings 

Your diagram should look like this. Filtering the companies does not effect the total revenue line.

image

Über erpcoder
Software Engineer and Dynamics AX enthusiast working in Research & Development for InsideAx

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden /  Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

w

Verbinde mit %s

%d Bloggern gefällt das: