Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 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
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.
@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)
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |