Summing a column with a filter on multiple columns

[Johannesvd]

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

[Eric_Zhang]

---

@Johannesvd 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

---

@Johannesvd

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.

[Johannesvd]

@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)