Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

BIN Range / Dynamic Segmentation based on measure

I have StoreId's and their sales(column name = RetailDollars). I have created a measure for totalsales -  Totalsales$ = SUM(SellThrough[RetailDollars])  I have created a BIN           T...
Need Help
Show and Tell
  • v-yuta-msft's avatar
    v-yuta-msft
    6 years ago

    Anonymous ,

     

    You can simply create a calculate column in the sales table using dax as below:

    Number of stores = CALCULATE(COUNT('Table'[StoreID]), FILTER('Table', 'Table'[Sales] >= Bin[Min_Value] && 'Table'[Sales] <= Bin[Max_Value]))

     

    Community Support Team _ Jimmy Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

v-yuta-msft's avatar
v-yuta-msft
Icon for Community Support rankCommunity Support
6 years ago

Anonymous ,

 

Could you please share the two tables sample data and give the expected result?

 

Regards,

Jimmy Tao

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Jimmy,

     

    This is my sample data set

     

     

     

     

     

     

    I would like to see how many stores made sales between 0 to 5000, 5000 to 50,000 and >50,000.

     

    Expected result

     

    • v-yuta-msft's avatar
      v-yuta-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      Anonymous ,

       

      You can simply create a calculate column in the sales table using dax as below:

      Number of stores = CALCULATE(COUNT('Table'[StoreID]), FILTER('Table', 'Table'[Sales] >= Bin[Min_Value] && 'Table'[Sales] <= Bin[Max_Value]))

       

      Community Support Team _ Jimmy Tao

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi 

        This is really helpful. It made my work easy but I would also like to see the store ID's that made total sales in that particular range (Gold,Platinum,Silver) for selected year. 

         

        For example I want to see what all storeIDs made total sales in the gold range for 2019.

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      6 years ago

      Hi,

      There should be only 1 store in the Gold bucket - StoreID 2.  You may download my PBI file from here.

      Hope this helps.