Forum Discussion

Cathryn's avatar
Cathryn
Helper III
8 years ago

Filter on Total Sum Measure possible?

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 pro...
Need Help
shebr's avatar
shebr
Resolver III
8 years ago

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's avatar
    Cathryn
    Helper III
    8 years ago

    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's avatar
      dedelman_clng
      Community Champion
      8 years ago

      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's avatar
        Cathryn
        Helper III
        8 years ago

        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"
))