BIN Range / Dynamic Segmentation based on measure

[Anonymous]

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

Then created a measure to count the number of stores

Sheet1(2) is Bin table name and sell through is sales tables name.

Results is showing something like this

which is not accurate.

Could someone please help me solve this issue ?
FYI : I have converted storeId's column to text which are actual numbers.

[v-yuta-msft]

@Anonymous ,

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

Regards,

Jimmy Tao

[Anonymous]

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

 

[Ashish_Mathur]

Hi,

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

Hope this helps.

[v-yuta-msft · Accepted solution]

@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]

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.

[TomMartens]

Hey @Anonymous ,

I guess this article: https://www.daxpatterns.com/dynamic-segmentation/

provides what you are looking for.

Regards,

Tom