Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Summing a column with a filter on multiple columns

Hi all,

 

Can someone help with a measure (or multiple) to sum the values in the column amount, per no.,per customer and only the GL accounts starting with an 8:

 

 

Result:

 

Customer 1234  amount 1080

Customer 4321 amount  2001

 

 

 

2 Replies

  • Eric_Zhang's avatar
    Eric_Zhang
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago
    Anonymous wrote:

    Hi all,

     

    Can someone help with a measure (or multiple) to sum the values in the column amount, per no.,per customer and only the GL accounts starting with an 8:

     

     

    Result:

     

    Customer 1234  amount 1080

    Customer 4321 amount  2001

     

     

     

    Anonymous

    You can get the expected output by creating two measures as below. You can see more details in the attaced pbix file.

    CustomerID = MAX(Table2[Customer])

Total Amount =
SUMX (
    FILTER ( Table2, LEFT ( Table2[GL], 1 ) = "8" && ISBLANK ( Table2[Customer] ) ),
    Table2[Amount]
)

     

    By the way, the provided sample data is not normalized. I have some concern about your data for further analysis, as I can see your table contains summized data(1081and 2081, the total for No) and raw data(the lines with blank customer). The blank customer is also an issue.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Eric_ZhangThanks! i was to quick marking this as the solution, but it helped me a few steps further.

       

      So the data keeps on going, for example customer 1234 is a returning customer.   I dont want to use the column "no" in the result i only want to see the sum of the general ledgers starting with an 8 per customer.

      Second question for learning purposes, why do u use :

       ISBLANK ( Table2[Customer])

      Is this an extra check on your filter?

       

      Thanks!

       

      I will check on the data (You may noticed im a newbee)