Filter on Total Sum Measure possible?

[Cathryn]

Hi Everyone

I've been trying to find a solution for my issue but have been unsuccesful so far.  

I am building a report that calculates the total number of records by customer.  I need to provide a filter so the user can search companies that have a TOTAL number of records > 1,000,000 for example.  I need all the visuals to respond to the filter so I can't just use the visual filter.  I realized that you can't place a meaure in a page level filter so I created a separate table that has just the customerid and record count but that doesn't work if they happen to filter the record types and want only customers that are filtered on, for example 'Green Cars' and have a total record count > 1,000,000.  So, I need it to be dynamic.

Has anyone found a solution for this?

Thank you!

Cathryn

[shebr]

Hi @Cathryn

Why don't you create a new column, and say something like "Status = IF([Total] > 1,000,000, "Over 1m", "Less than 1m")" and use that as your page filter?

Let me know how you get on.

Thanks

shebr

[Cathryn]

I wrote a switch statment but the Summation of the record counts isn't being recognized:

RU Status = SWITCH(
TRUE(),
SUM('BI CONNECT Usage'[RecordsUploaded]) > 1000000, "Over 1 mil",
SUM('BI CONNECT Usage'[RecordsUploaded]) > 500000 && SUM('BI CONNECT Usage'[RecordsUploaded]) < 999999, "Between 500K and 1 Mil",
SUM('BI CONNECT Usage'[RecordsUploaded]) < 499999, "Less than 500K"
)

They just all return 'Over 1 mil'

Do I need to format something?  Or put CALC around the SUM?

[dedelman_clng]

Assuming that the new table you are referring to is a SUMMARIZE type table, with the CustomerID linked to the main customer table, for the additional calculated column on that table you will want to use IF instead of SWITCH (SWITCH and ranges or inequalities don't work well together).

RANGE =
  IF(RecordCount >= 1000000, "Over 1 Mil",
    IF(RecordCount >= 500000, "Between 500,000 and 1 Mil", "Below 500,000"))

Hope this helps

David

[Cathryn]

Thanks David - I was able to create the measure but I still can't filter by it.  Now I need to allow the user to choose those buckets but if I try to use that field in a slicer, it only shows one value.  

I found that maybe the "What If" would work.  I'm playing around with that right now but I'm not sure that will work either.  All the visuals have to filter based on that bucket.

Here's the code for the bucket which is the 1st table.  

Upload Bucket Count = 
	IF(SUM('BI CONNECT Usage'[RecordsUploaded]) > 1000000, "Over 1 mil", 
		IF(SUM('BI CONNECT Usage'[RecordsUploaded]) >= 500000, "Between 500K and 1 Mil", 
		"Less than 500K"
))

[dedelman_clng]

Hi @Cathryn

Based on the screen shot you sent, try this.  You will want to create *calculated columns* on [BI CONNECT Customers] instead of measures - the columns can then be used in slicers/filters.

NOTE: This assumes a 1:many relationship between "BI CONNECT Customers" and "BI CONNECT Usage"

Total Records = 
SUM(RELATED("BI Connect Usage"[RecordsUploaded]))

Upload Bucket Count =
	IF("BI CONNECT Customer"[Total Records] > 1000000, "Over 1 mil", 
		IF("BI CONNECT Customer"[Total Records] >= 500000, "Between 500K and 1 Mil", 
		"Less than 500K"
))

Hope this helps,

David