Using distinctcount on summed rows

hpandersen

New Member
Joined
Dec 12, 2018
Messages
1
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

<tbody>
</tbody>


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?
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Hi,

For BI, i'd add a measure AmountSum = CALCULATE(SUM(Tablename[Amount]),Tablename[Year])

Have Customer and Year in a table, and have a visual level filter on AmountSum > 1500
 
Last edited:
Upvote 0
That measure isn't quite right. But you need to sum the amount for the customer and the year.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top