Getting sum of a column of distinct rows from a filtered table

[CJLastimosa07]

Hi,

I have 3 Tables:

Account:

Account ID	Account Name
123456789	Company A
234567891	Company B

Employee

Account Name	Employee ID	Employee
Company A	E001	John
Company A	E002	James
Company B	E003	Will

Connections

Connection ID	Client ID	Client	Category	Sales	Account Name	Employee ID	Employee
C0001	BU0001	Alpha	1	1,000	Company A	E001	John
CL002	BU0001	Alpha	1	1,000	Company A	E002	James
CL003	BU0002	Beta	1	2,000	Company A	E002	James
CL004	BU0003	Charlie	2	3,000	Company A	E002	James
CL005	BU0004	Delta	1	4,000	Company B	E003	Will
CL006	BU005	Echo	2	3,000	Company B	E003	Will

I want to add another column in the Accounts table called "Total Sales per Category 1 Clients".

The idea is to get the sum of the 'Sales' value for distinct 'Client IDs' for "Category 1" clients only.

So the result would be:

Account ID	Account Name	Total Sales per Category 1 Clients
123456789	Company A	3,000
234567891	Company B	4,000

Is this possible using DAX?

Thank you.

[SachinNandanwar]

Why is the Total for Company A 3000 ?

Shouldnt it be 4000 ?

[Total Sales Measure]= CALCULATE(SUM(Connections[Sales]),Connections[Category]=1)

[CJLastimosa07]

Hi Sachin,

It should only sum the sales for distinct clients of the company.

[SachinNandanwar]

But what is the business rule to decide the value of Sales to be used in the calculation for a distinct client ?

Lets assume Clinet Alpha has 2 unique Sales value 

Which one should be used 1000 or 2000 ?

[CJLastimosa07]

It's a column from another table (Clients). It's just added in the Connections table via LOOKUPVALUE, so it's always the same for the same client.

[SachinNandanwar · Accepted solution]

Create this measure :

Total Sales Measure = SUMX (VALUES(Connections[Client ID]),CALCULATE(MAX(Connections[Sales]),Connections[Category] = 1 ))

[CJLastimosa07]

It worked!

Thank you very much!

[Kedar_Pande]

New calculated column

Total Sales per Category 1 Clients = 
SUMX(
FILTER(
Connections,
Connections[Category] = 1 && 
Connections[Account Name] = EARLIER(Account[Account Name])
),
DISTINCT(Connections[Client ID]),
Connections[Sales]
)

Ensure that your tables have the correct relationships set up:

- Account Table: Account ID should relate to Account Name in both the Employee and Connections tables.
- Connections Table: Account Name should relate to the Account table based on the Account Name field.

If this helped, a Kudos 👍 or a Solution mark would be awesome! 🎉

Cheers,

Kedar Pande

Connect on LinkedIn

[CJLastimosa07]

Hi,

It's giving me this error: