cancel
Showing results for
Did you mean:
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:

 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?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV

## Re: Counting summed rows

Yeah, it should be:

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

---------------------------------------

Putting square pegs in round holes since 1972.

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Datanaut!

4 REPLIES 4
Helper I

## Re: Counting summed rows

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

Super User IV

## Re: Counting summed rows

Perhaps:

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

---------------------------------------

Putting square pegs in round holes since 1972.

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Datanaut!

Frequent Visitor

## Re: Counting summed rows

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?

Super User IV

## Re: Counting summed rows

Yeah, it should be:

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

---------------------------------------

Putting square pegs in round holes since 1972.

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Datanaut!

Announcements

#### ‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors