Using distinctcount on 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:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Year[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]2018[/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]2018[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]2018[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]2018[/TD]
[TD]800[/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]2018[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]2018[/TD]
[TD]700[/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]2018[/TD]
[TD]600[/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]2018[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]2018[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]


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?

 

[mrshl9898]

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

 

[mrshl9898]

That measure isn't quite right. But you need to sum the amount for the customer and the year.