Column: Total per customer

[ythiart]

HI all

I am quite new to Power BI and Dax and would appreciate your help.

I have two tables, one has the customer information like credit limit, etc and is one line per customer. The other table contains all the open items for the customers, e.g. outstanding invocies, unallocated payments, etc. This table has many lines per customer. I have joined them by customer number

In the customer information table I would like to add a column with the total per customer. 

My formula is this : 

Total = sum(custage_duedate[Amount in local currency])

I can easily do this in SQL and just add it to the table that way but I would like to learn how DAX works.

[Anonymous · Accepted solution]

// If your 'Customer Info' table is a big one
// you can still add a calculated column to it
// but I'd advise against using CALCULATE
// since it may be very slow. CALCULATE
// requires context transition and this is
// costly. You'll be better off using this
// instead (which does not use CALCULATE):

[Cust Total] = // calculated column in 'Customer Info'
var __currentCust = 'Customer Info'[CustId]
return
SUMX(
	filter(
		'Customer Info',
		'Customer Info'[CustId] = __currentCust
	),
	'Customer Info'[Amount]
)

Best

D

[harshnathani]

Hi @ythiart ,

You can just use this measure

Total = sum(custage_duedate[Amount in local currency])

Pull Customer No from  customer information Table  and this Total Measure which you have created to the Visualization Tab.

It will show you the Total Sum based on your customer No.

Else can you share the structure of the 2 tables and the sample data in Text Format.

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[harshnathani]

HI @ythiart ,

You can try

Total = CALCULATE (sum(custage_duedate[Amount in local currency]), ALLEXCEPT( custage_duedate, custage_duedate [ Customer_Id))

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!