Get percent of total in DAX using ALLSELECTED()

To get percent of total in DAX we can use the ALLSELECTED function. To demonstrate how this can be achieved I will make 3 calculated measures in my model.

1. TotalSales:=CALCULATE(SUM([Order Quantity]);ALLSELECTED())
2. Sum Order Quantity:=Sum([Order Quantity])
3. Percent Of Total:=[Sum Order Quantity] / [TotalSales]

The first measure will give the total quantity. The second one will give sum quantity for each dimension member. The last one will give the percent. To make the last one nice and readable you can format it as percent.

This is how it will look in an Excel pivot

Percent of total

This easy approach will only work on the first level in a hierarchy. To make it work on multiple levels you have to extend the code. But that will be an other post 🙂

Advertisements

Add a filter to your query using the “Filter” function

It is really easy to add filters to your DAX query. In this post I will show you some samples.

Lets take a look at the following query:

EVALUATE
FILTER (
SUMMARIZE (
'Internet Sales',
'Date'[Calendar Year],
'Date'[Month Name],
"Sales", 'Internet Sales'[Internet Total Sales]
),
'Date'[Calendar Year] = 2007
)

This will give you sales per year per month for year 2007.

The MDX for the same resultset might look like this (you can of course move the year filter to the where clause)

SELECT [Measures].[Internet Total Sales] on 0,
[Date].[Calendar Year].&[2007] * [Date].[Month Name].[Month Name] on 1
from [Internet Operation]

In the next query I have added another filter

EVALUATE
(
FILTER(
SUMMARIZE
(
'Internet Sales',
'Date'[Calendar Year],
'Date'[Month Name],
"Sales", 'Internet Sales'[Internet Total Sales]
), 'Date'[Calendar Year] = 2007 && 'Internet Sales'[Internet Total Sales] > 500000
)
)

This new condition will remove all rows with sales less than 500000 for year 2007

The equal query in MDX will look like

SELECT [Measures].[Internet Total Sales] on 0,
FILTER([Date].[Calendar Year].&[2007] * [Date].[Month Name].[Month Name],[Measures].[Internet Total Sales] > 500000) on 1
from [Internet Operation]

The next sample will be a little more complex. It will give you sales per year per month compared to the years total. I am not sure if this is the most easy way of doing this. Or the best way 🙂

DEFINE
MEASURE 'Internet Sales'[Sum Year] =
CALCULATE (
'Internet Sales'[Internet Total Sales],
ALLSELECTED ()
)
MEASURE 'Internet Sales'[Percent of year] =
FORMAT (
CALCULATE (
'Internet Sales'[Internet Total Sales]
/ 'Internet Sales'[Sum Year]
),
"Percent"
)
EVALUATE
ADDCOLUMNS (
FILTER (
SUMMARIZE (
'Internet Sales',
'Date'[Calendar Year],
'Date'[Month Name],
"Sales", 'Internet Sales'[Internet Total Sales]
),
'Date'[Calendar Year] = 2007
),
"Year", 'Internet Sales'[Sum Year],
"Percent of year", 'Internet Sales'[Percent of year]
)

The get a similar resultset in MDX we can use this query

WITH MEMBER [Measures].[Sum Year] AS
( Root ( [Date].[Calendar Year] ),
[Measures].[Internet Total Sales] )
MEMBER [Measures].[Percent of year] AS
( [Measures].[Internet Total Sales] / [Measures].[Sum Year] ),
FORMAT_STRING = 'Percent'
SELECT
{ [Measures].[Internet Total Sales], [Measures].[Sum Year], [Measures].[Percent of year] } ON 0,
[Date].[Calendar].[Year].&[2007] * [Date].[Month Name].Children ON 1
FROM [Internet Operation]