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
hpandersen
Frequent Visitor

Counting summed rows

Hi.

I have a tabel with a lot of customer financial transactions.
One customer can easily have 100 transactions per year.
Example:

CustomerYearAmount
1012018250
1012018500
1012018300
1022018800
1022018300
1022018700
1032018600
1032018200
1032018100



I would like to sum the different transaction per customer per year and the count how many of these summed transaction amount is >1500
In this excample, the sum amount for customer 102 is 1800 and should be counted as one.

I have made this counting in a pivot in Excel, but I really can't figure out how to do it in Power BI.
Any suggestions?

1 ACCEPTED SOLUTION

Yeah, it should be:

 

Measure = 
VAR __table = SUMMARIZE('Table',[Customer],[Year],"__TotalAmount",SUM([Amount]))
RETURN
COUNTX(FILTER(__table,[__TotalAmount]<1500 && [__TotalAmount]>1000),[Customer])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

Perhaps:

 

Measure = 
VAR __table = SUMMARIZE('Table',[Customer],[Year],"__TotalAmount",SUM([Amount]))
RETURN
COUNTX(FILTER(__table,[__TotalAmount]>1500),[Customer])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler

 

That did the job, thanks.

 

Just one additional question: what if I would like to count amounts between 1000 and 1500?

I guess I should add one more filter to the COUNTX. I tried that, but it didn't work for me.

Can you help?

Yeah, it should be:

 

Measure = 
VAR __table = SUMMARIZE('Table',[Customer],[Year],"__TotalAmount",SUM([Amount]))
RETURN
COUNTX(FILTER(__table,[__TotalAmount]<1500 && [__TotalAmount]>1000),[Customer])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

hi @hpandersen,

 

You can create calculated measures for Sum and Count.

 

For sum, create a calculated measure using

Sum = CALCULATE(sum(Sheet1[Amount]), GROUPBY(Sheet1, Sheet1[Customer]))
 
And, for Count, create a calculated measure using
Count = IF([Sum]>1500,1, 0)
 
Now, When you can see as below
CustomerSumYear
10110500
10218001
1039000

 

 

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.