Power BI DAX Percentage

There are a couple of ways in Power BI to create a chart to show percentage and I am writing this post to show few different ways to accomplish it.

I am using a few tables from AdventureWorks 2016 CTP3 database, as below:

For testing propose, I will use Line Total (Sales Order Detail Table) , Product Category (Product Category Table) and Calendar Year (DimDate), as bellow:

% Data.png

100% Stacked Column chart

The quickest way to build a percentage chart is to use “100% Stacked Column chart” which does not require any DAX code. I added “LineTotal” to Value, “Product Category” to Legend and “Calendar Year” to Axis. See below:

% 100% Stacked Fields.png

This is the result:

In the chart above, you can see that 91.83% of revenue in 2011 is for product category Components.

The 100% Stacked Column chart is available by default in Power BI visualization panel:

DAX Percentage calculation

This option you will need to build a few measures using DAX as below:

Denominator = CALCULATE(SUM('Sales SalesOrderDetail'[LineTotal]),ALLSELECTED('Production ProductCategory'[Product Category]))

Numerator = Sum('Sales SalesOrderDetail'[LineTotal])

% Revenue = DIVIDE([Numerator],[Denominator])

Using the measure “% Revenue” you can see the same result in a Clustered column chart, as bellow:

Why should I use the above measure?

You can use the above measure in different visuals, such as, “Line and Clustered column chart”, “Line chart”, “Area chart” and others. Also, you can extend the DAX calculation to more specific requirement and you are not restricted to “100% Stacked Column chart”. See a few chart sample below:

 

2 thoughts on “Power BI DAX Percentage

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: