Forum Discussion

misul's avatar
misul
Icon for Helper I rankHelper I
8 years ago
Solved

Binning data and Distinct Count error

Hello, I would appreciate any general guidance/info on this topic.   I have a simple sales table and I am trying to group (bin) all products into categories of <15, 15-100 and >100.    To catego...
Need Help
Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

HI misul

 

Why not add a calculated column to your table like this

 

Bucket = 
    SWITCH(
        TRUE() ,
        'Table1'[Sales] < 15 , "< 15" ,
        'Table1'[Sales] > 100 , "More than 100" ,
        "15-100"
        )

 

 

 

  • misul's avatar
    misul
    Icon for Helper I rankHelper I
    8 years ago

    hi Phil_Seamark

    thanks for your comment. However, I am looking for count of Product, not count of Sales. In other words, trying to answer the question "Which are the products that had total sales of <15 in this year?" For 2016, the answer should be 3 products.

     

    I applied your Switch formula ,but it doesn't solve this. 

     

     

    • misul's avatar
      misul
      Icon for Helper I rankHelper I
      8 years ago

      Some further info: 

      The final goal is apply the same buckets on a global as well as regional level. This means, in my understanding, that the bucket applied to each row should be applied  AFTER the filters are year/region are implented.

       

      That's why I tried to use a measure rather than a calculated column. Or perhpas a combination of the two (which I haven't figured out yet).