PowerPivot Filtering DAX Formula

[themule1406]

I have a table with invoices and revenue.

Invoice Revenue
A 200
B 600
A 500
C 400

The output I'm trying to get is the number of unique invoices less than 500. So this example, it would be one invoice, "C" for 400.

The DAX formula below, sees two distinct invoices ,"A" and "C".
Calculate(distinctcount([Invoice],filter(Table1,sum(Table1[Revenue])<500))H

How can I change my formula to get this to work as I hope.

<colgroup><col width="82" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;"> <tbody> </tbody>

<colgroup><col width="82" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;"> <tbody> </tbody>

 

[SOQLee]

Invoice <500:=IF([Sum of Revenue]<500,[Sum of Revenue])

 

[themule1406]

While this does appropriately filter, it does not sum. I tried to use SumX(Table1,if([sum of revenue]<500, [sum of revenue])) to force to sum, but it then reverts to sum 200 + 400.

 

[themule1406]

I think that I need to apply a filter of unique Invoices and then sumx the statement...

 

[SOQLee]

This should work....

Invoice <500 FIXED:=IF([Sum of Revenue]<500,[Sum of Revenue],SUMX(DISTINCT(Range[Invoice]),[Invoice <500]))