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)
DimSalesReason (Sales Reason)
FactInternetSales (Internet Sales)
FactInternetSalesReason (Internet Sales Reason)
Columns (renamed columns) - Table
EnglishCountryRegionName (Country) – Geography
SalesReasonName (Sales Reason) – Sales Reason
This is how you typically calculate totals:
Sales1 = SUM('Internet Sales'[SalesAmount])
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])
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])
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]))
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])
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!
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. In addition, Accelebrate offers the class publicly online for individuals.
In-Depth Power BI Training
For in-depth Power BI training, click here to view
Accelebrate’s training classes are available for private groups of 3 or more people at your site or online anywhere worldwide.
Don't settle for a "one size fits all" public class! Have Accelebrate deliver exactly the training you want, privately at your site or online, for less than the cost of a public class.
For pricing and to learn more, please contact us.Contact Us
New York City
Salt Lake City