Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
__Marc__
Frequent Visitor

SUM doesn't filter on region

I have 2 tables:

 

Transactions:

Customer code    Region    Other columns ....
1A 
1B 
2A 

 

Total sales per customer:

Customer code      Total sales amount
1100
250
  

 

I want to create a diagram that show the total per region:

 

A = 150

B = 100

 

What I get is:

A = 250

B = 250

 

The tables are connected on Customer code using many (Transactions) to 1 (Total sales per customer).

 

The diagram view uses:
- Row: Region column

- Value: SUM([Total sales amount])

 

How can I solve this?

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

The reason that you're getting the same result for every row is that the filter cannot pass from the Transactions table to the Total Sales Per Customer because it is a many-to-one relationship. You will need to iterate over the transactions table and sum the related value from the Total Sales Per Customer table

Total Sales =
SUMX( Transactions, RELATED('Total Sales Per Customer'[Total Sales amount]) )

View solution in original post

4 REPLIES 4
VahidDM
Super User
Super User

Hi @__Marc__ 

 

Change the Cross filter direction to the Both and that will sort out your issue.

VahidDM_0-1645793863794.png

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

johnt75
Super User
Super User

The reason that you're getting the same result for every row is that the filter cannot pass from the Transactions table to the Total Sales Per Customer because it is a many-to-one relationship. You will need to iterate over the transactions table and sum the related value from the Total Sales Per Customer table

Total Sales =
SUMX( Transactions, RELATED('Total Sales Per Customer'[Total Sales amount]) )
tej147
Frequent Visitor

Use SUMX instead of SUM

SUMX is the iterative function which performs on row-level.

Sales = SUMX([Total sales amount])

tej147
Frequent Visitor

Following are the two methods using CROSSFILTER and RELATED dax functions.

pbi.png

 

 

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.