Forum Discussion

ythiart's avatar
ythiart
Regular Visitor
6 years ago
Solved

Column: Total per customer

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's avatar
    Anonymous
    6 years ago
    // 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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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!

     

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    // 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