Top 6 Power BI Desktop Tricks

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.

custom 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
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 subtotal from total

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])

excluding measure value from total

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]))

converting a measure value

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])

subtracting a subtotal

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]

many-to-many total

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-Depth Power BI Training

For in-depth Power BI training, click here to view
Accelebrate's Microsoft Azure Data Fundamentalse courses for you and your staff.

Request Pricing

Learn faster

Our live, instructor-led lectures are far more effective than pre-recorded classes

Satisfaction guarantee

If your team is not 100% satisfied with your training, we do what's necessary to make it right

Learn online from anywhere

Whether you are at home or in the office, we make learning interactive and engaging

Multiple Payment Options

We accept check, ACH/EFT, major credit cards, and most purchase orders



Recent Training Locations

Alabama

Birmingham

Huntsville

Montgomery

Alaska

Anchorage

Arizona

Phoenix

Tucson

Arkansas

Fayetteville

Little Rock

California

Los Angeles

Oakland

Orange County

Sacramento

San Diego

San Francisco

San Jose

Colorado

Boulder

Colorado Springs

Denver

Connecticut

Hartford

DC

Washington

Florida

Fort Lauderdale

Jacksonville

Miami

Orlando

Tampa

Georgia

Atlanta

Augusta

Savannah

Hawaii

Honolulu

Idaho

Boise

Illinois

Chicago

Indiana

Indianapolis

Iowa

Cedar Rapids

Des Moines

Kansas

Wichita

Kentucky

Lexington

Louisville

Louisiana

New Orleans

Maine

Portland

Maryland

Annapolis

Baltimore

Frederick

Hagerstown

Massachusetts

Boston

Cambridge

Springfield

Michigan

Ann Arbor

Detroit

Grand Rapids

Minnesota

Minneapolis

Saint Paul

Mississippi

Jackson

Missouri

Kansas City

St. Louis

Nebraska

Lincoln

Omaha

Nevada

Las Vegas

Reno

New Jersey

Princeton

New Mexico

Albuquerque

New York

Albany

Buffalo

New York City

White Plains

North Carolina

Charlotte

Durham

Raleigh

Ohio

Akron

Canton

Cincinnati

Cleveland

Columbus

Dayton

Oklahoma

Oklahoma City

Tulsa

Oregon

Portland

Pennsylvania

Philadelphia

Pittsburgh

Rhode Island

Providence

South Carolina

Charleston

Columbia

Greenville

Tennessee

Knoxville

Memphis

Nashville

Texas

Austin

Dallas

El Paso

Houston

San Antonio

Utah

Salt Lake City

Virginia

Alexandria

Arlington

Norfolk

Richmond

Washington

Seattle

Tacoma

West Virginia

Charleston

Wisconsin

Madison

Milwaukee

Alberta

Calgary

Edmonton

British Columbia

Vancouver

Manitoba

Winnipeg

Nova Scotia

Halifax

Ontario

Ottawa

Toronto

Quebec

Montreal

Puerto Rico

San Juan