Power BI – How to Create Customized and Conditional Totals

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. 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 Analyzing Data with Power Bi course courses for you and your staff.

 

Contact Us:

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 Train For Us

Toll-free in US/Canada:
877 849 1850
International:
+1 678 648 3113

Toll-free in US/Canada:
866 566 1228
International:
+1 404 420 2491

925B Peachtree Street, NE
PMB 378
Atlanta, GA 30309-3918
USA

Subscribe to our Newsletter:

Never miss the latest news and information from Accelebrate:

Microsoft Gold Partner

Please see our complete list of
Microsoft Official Courses

Recent Training Locations

Alabama

Huntsville

Montgomery

Birmingham

Alaska

Anchorage

Arizona

Phoenix

Tucson

Arkansas

Fayetteville

Little Rock

California

San Francisco

Oakland

San Jose

Orange County

Los Angeles

Sacramento

San Diego

Colorado

Denver

Boulder

Colorado Springs

Connecticut

Hartford

DC

Washington

Florida

Fort Lauderdale

Miami

Jacksonville

Orlando

Saint Petersburg

Tampa

Georgia

Atlanta

Augusta

Savannah

Idaho

Boise

Illinois

Chicago

Indiana

Indianapolis

Iowa

Ceder Rapids

Des Moines

Kansas

Wichita

Kentucky

Lexington

Louisville

Louisiana

Banton Rouge

New Orleans

Maine

Portland

Maryland

Annapolis

Baltimore

Hagerstown

Frederick

Massachusetts

Springfield

Boston

Cambridge

Michigan

Ann Arbor

Detroit

Grand Rapids

Minnesota

Saint Paul

Minneapolis

Mississippi

Jackson

Missouri

Kansas City

St. Louis

Nebraska

Lincoln

Omaha

Nevada

Reno

Las Vegas

New Jersey

Princeton

New Mexico

Albuquerque

New York

Buffalo

Albany

White Plains

New York City

North Carolina

Charlotte

Durham

Raleigh

Ohio

Canton

Akron

Cincinnati

Cleveland

Columbus

Dayton

Oklahoma

Tulsa

Oklahoma City

Oregon

Portland

Pennsylvania

Pittsburgh

Philadelphia

Rhode Island

Providence

South Carolina

Columbia

Charleston

Spartanburg

Greenville

Tennessee

Memphis

Nashville

Knoxville

Texas

Dallas

El Paso

Houston

San Antonio

Austin

Utah

Salt Lake City

Virginia

Richmond

Alexandria

Arlington

Washington

Tacoma

Seattle

West Virginia

Charleston

Wisconsin

Madison

Milwaukee

Alberta

Edmonton

Calgary

British Columbia

Vancouver

Nova Scotia

Halifax

Ontario

Ottawa

Toronto

Quebec

Montreal

Puerto Rico

San Juan

© 2013-2019 Accelebrate, Inc. All Rights Reserved. All trademarks are owned by their respective owners.