We offer private, customized training for 3 or more people at your site or online.
A total is not always a simple summation. Here are a few examples of situations you are likely to meet that involve customizing totals.
All the data is from the SQL Server sample relational database, AdventureWorks DW, so you can try this out if you wish. The screenshots are from Power BI Desktop but you can also use Excel pivot tables. In Power BI Desktop, I have used the DAX SELECTEDVALUE() function. If you build your model in Excel Power Pivot or SSAS Tabular, this function may not be available, depending on which version you are using. In that case, use IF(HASONEVALUE(VALUES())) instead.
Also, in Excel and SSAS, remember to add a colon to the measure definitions later in this article. All measures are formatted as US dollars with no decimal places – except for UK sales for the measure Sales6 (see below).
Tables (renamed tables)
DimGeography (Geography)
DimCustomer
DimSalesReason (Sales Reason)
FactInternetSales (Internet Sales)
FactInternetSalesReason (Internet Sales Reason)
Columns (renamed columns) - Table
EnglishCountryRegionName (Country) – Geography
SalesReasonName (Sales Reason) – Sales Reason
Default totals
This is how you typically calculate totals:
Sales1 = SUM('Internet Sales'[SalesAmount])
Excluding an attribute's subtotal from totals
Suppose we do not want the UK to count towards our global total. Here are two alternatives:
Sales2 = SWITCH(SELECTEDVALUE(Geography[Country]), "United Kingdom", BLANK(), SUMX(FILTER(Geography, Geography[Country] <> "United Kingdom"), [Sales1]))
Sales3 = SWITCH(SELECTEDVALUE(Geography[Country]), BLANK(), SUMX(FILTER(Geography, Geography[Country] <> "United Kingdom"), [Sales1]), [Sales1])
Excluding a measure value from totals
Maybe we are not interested in sales of less than $2m. Again, two alternatives:
Sales4 = SWITCH(SELECTEDVALUE(Geography[Country]), BLANK(), SUMX(FILTER(VALUES(Geography[Country]), [Sales1] > 2000000), [Sales1]), IF([Sales1] < 2000000, BLANK(), [Sales1]))
Sales5 = SWITCH(SELECTEDVALUE(Geography[Country]), BLANK(), SUMX(FILTER(VALUES(Geography[Country]), [Sales1] > 2000000), [Sales1]), [Sales1])
Converting a measure value in totals
This is useful when your measures are recorded in different units:
Sales6 = SWITCH(SELECTEDVALUE(Geography[Country]), BLANK(), "NA", "United Kingdom", FORMAT([Sales1], "£#,###"), FORMAT([Sales1], "$#,###"))
Sales7 = SUMX(VALUES(Geography[Country]), IF(Geography[Country] = "United Kingdom", [Sales1] * 1.5, [Sales1]))
Subtracting an attribute's subtotal from totals
This is relevant when you are dealing with accounts. For example, net income would be gross income minus expenses. Two versions:
Sales8 = SUMX(VALUES(Geography[Country]), IF(Geography[Country] = "United Kingdom", - [Sales1], [Sales1]))
Sales9 = SWITCH(SELECTEDVALUE(Geography[Country]), BLANK(), SUMX(VALUES(Geography[Country]), IF(Geography[Country] = "United Kingdom", - [Sales1], [Sales1])), [Sales1])
Many-to-many totals
Sales1, which is not a summation, is correct. Sales10, a summation, is incorrect. Generally, in a many-to-many situation, the total will be less than a simple summation. Here are the two measures used:
Sales1 = SUM('Internet Sales'[SalesAmount])
Sales10 = IF(HASONEVALUE('Sales Reason'[Sales Reason]), [Sales1], SUMX('Sales Reason', [Sales1]))
Sales1 is very straightforward, but a little work is required first. Add the following two columns (with the same names) to Internet Sales and to Internet Sales Reason:
ReasonKey = 'Internet Sales'[SalesOrderNumber] & 'Internet Sales'[SalesOrderLineNumber]
ReasonKey = 'Internet Sales Reason'[SalesOrderNumber] & 'Internet Sales Reason'[SalesOrderLineNumber]
Create a relationship using these columns. Make the relationship from Internet Sales to Internet Sales Reason bi-directional. Check there is a relationship from Sales Reason to Internet Sales Reason. Then the magic should happen – Sales1 will not double-count a sale that has been made for two reasons (e.g. a customer made a purchase based on price and after seeing a TV ad). Sales10 artificially double counts and is incorrect.
Our differing totals for sales – take your pick!
$22,575,253
$25,966,965
$27,380,832
$29,358,677
$30,856,532
$31,054,533
NA
And, depending on your situation, any one of them is correct – apart from $30,856,532 which is double-counting.
To master even more skills for customizing totals and using Power BI to its fullest, consider purchasing a private delivery of MOC 20778 - Analyzing Data with Power BI training for your team. To do so, request a quote.
