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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
idarif2007
Regular Visitor

Add Sum on column based on customer_id which cannot be used as a Group in Tablix

Hello,

 

I have a requirement where i want to check the number of customer who have transaction amt greater then 0, As Customer is a detail row it cannot be used in the tablix as i just need the number of customer's with positive transaction, 

 

famillie# customer with positve transaction
A10
B12
C13

 

for example For famillie A there are 10 Customers with different transaction as positive as well as negative but their sum is positive.

i am trying to use an expression as 

=Sum(IIf(Sum(Fields!CAAnne.Value,Fields!CUSTOMER_ID.Value)>0,1,nothing))

This gives me error as it is not in scope.

 

I hope i am able to explain my situation, please let me know if you have further queries.

 

Regards,

Arif

5 REPLIES 5
idarif2007
Regular Visitor

Can someone please help!

Anonymous
Not applicable

Hi @idarif2007 ,

In order to better understand your demands and give the right solution, could you please provide some more specific information? such as your desensitized example data and a screenshot of your desired results?

Thanks for your efforts & time in advance.

 

Best regards,
Community Support Team_ Binbin Yu

Hello,

 

for a product family there are N customers who would have bought the product, There could be negative transaction as well. so for a customer who has 2 trnasction one is positive "10" and other is negative "(-20)" and if i sum them it would be -10, so i just want to count the distinct customer who has positive transaction after sum.

As my report tablix is at Product family level i cannot add customer  as it very detail data, so i would like to have  acondition to check the sum of Amount at customer and check if it is greater then 0 and if it greater then 0 then include the customer in the count. 

Anonymous
Not applicable

Hi @idarif2007 ,

Could you please provide desensitized example data and a screenshot of your desired results?

Thanks for your efforts & time in advance.

 

Best regards,
Community Support Team_ Binbin Yu

 

Hello Team,

 

I am currently getting below records when i use count distinct and check the records to be greater then 0

Famillie productCnt customer with Positive transaction
A10
B20
C30

 

Now if you check the detail records for famille product A

 

FamilllieCAnneCustomer_id
A10A11
A-9A11
A10A12
A15A12
A10A13
A16A13
A-33A13
A10A14
A20A15
A30A15
A40A16
A20A17
A10A17
A10A18
A70A19
A80A19
A90A19
A100A19
A10

A20

 

I should only get the customer count as 9 for Famillie product A as for customer "A13" the sum of CAnne will be negative.

Famillie productCAnneCustomer_idMaterial
A10A11MAT_A
A-9A11MAT_B
A10A12MAT_A
A15A12MAT_B
A10A13MAT_A
A16A13MAT_B
A-33A13MAT_C
A10A14MAT_A
A20A15MAT_B
A30A15MAT_C
A40A16MAT_A
A20A17MAT_B
A10A17MAT_C
A10A18MAT_A
A70A19MAT_B
A80A19MAT_C
A90A19MAT_D
A100A19MAT_E
A10A20MAT_F

Can you please help!

 

REgards,

Arif

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.