Forum Discussion

hpandersen's avatar
hpandersen
Frequent Visitor
7 years ago
Solved

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: Customer Year Amount 101 2018 250 101 2018 500...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    7 years ago

    Yeah, it should be:

     

    Measure = 
VAR __table = SUMMARIZE('Table',[Customer],[Year],"__TotalAmount",SUM([Amount]))
RETURN
COUNTX(FILTER(__table,[__TotalAmount]<1500 && [__TotalAmount]>1000),[Customer])
hpandersen's avatar
hpandersen
Frequent Visitor
7 years ago

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's avatar
Greg_Deckler
Community Champion
7 years ago

Yeah, it should be:

 

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