Forum Discussion
Distinct Count with Filter
I want to count the number of clients who have an average order amount greater than $1000. In my table, I can clearly see there should be 16, but my counting measure is returning 7 and I have no idea why. You can see the rows it is counting in the table as well.
2016 # Accounts Avg Booked Order >$1000 = CALCULATE(DISTINCTCOUNT('Order Header-Bookings'[WHOLE_S_ID]), FILTER('Order Detail-Bookings', [Avg 2016 Booked $ Per Order] > 1000))Avg 2016 Booked $ Per Order = DIVIDE([2016 Booked$], CALCULATE(DISTINCTCOUNT('Order Detail-Bookings'[ORDER_ID]), FILTER('Date Table', [Year] = "2016")))2016 Booked$ = CALCULATE(sum('Order Detail-Bookings'[BookedDollars]),Filter('Date Table','Date Table'[Year] = "2016"))
Thanks!!
The problem seems to have been fixed by moving my measures to a different table in my report. Thanks everyone!
4 Replies
just air code
Count Column > 1000 = IF( [Avg 2016 Booked $ per Order] >= 1000, 1, BLANK() )
should put a 1 when over 1000 that can then be counted.....
I have thought about doing this but the query I'm pulling is giant (more than 2 million rows of data). I need to do this multiple times for >1000, > 500, <200, and all for 2016 and 2017. If I have to create calculated columns for all of these measures, I'm afraid it would make the report run slow as I already have trouble with memory usage, etc.
I am not able to reproduce your issue using my sample data. Could you please share sample data of 'Order Detail-Bookings' table 'Order Header-Bookings' that can be copied and pasted so that I can test? We also need to know which field you use to create relationship between the two tables.
Regards,
LydiaThe problem seems to have been fixed by moving my measures to a different table in my report. Thanks everyone!
