Counting summed rows

[hpandersen]

Hi.

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

Customer	Year	Amount
101	2018	250
101	2018	500
101	2018	300
102	2018	800
102	2018	300
102	2018	700
103	2018	600
103	2018	200
103	2018	100

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?

[Greg_Deckler]

Perhaps:

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

[hpandersen]

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?

[Greg_Deckler · 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])

[Anonymous]

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

Customer	Sum	Year
101	1050	0
102	1800	1
103	900	0