Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Johannesvd
Helper I
Helper I

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:

 

2017-09-27_13-43-58.jpg

 

Result:

 

Customer 1234  amount 1080

Customer 4321 amount  2001

 

 

 

2 REPLIES 2
Eric_Zhang
Employee
Employee


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

 

2017-09-27_13-43-58.jpg

 

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

Capture.PNG

 

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.

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.