Dynamic segments calculation for each month or week

[Anonymous]

Hello everyone!

This may be easily solved but I can't get my head around it. I have sales data which has shop ID, date, quantity, city etc. as shown below

what I want to achieve in Power BI is the following, I want to create a table as shown below, where it sums unique shops by segments so for example 100 shops reside in 1/5 segment, and these segments are ordered from top to bottom (high sales to low).

so the first bucket which has 100 shops in it, it's also the most selling bucket as you see it has the highest sales, and then the rest of the calculation comes i.e. weighted sales (divide each segment with the total sales)

and also note I want to have a date filter and city for example when you choose November, everything should be calculated and reordered from scratch because some shops may have high sales in November but no sales in October 

 for more context, this can be easily achieved in excel for example

1. you sumifs by Shop (you will have sales by shop)
2. then you will order them (high to low)
3. assign buckets to them
4. calculate for each bucket with IF conditions

[Anonymous]

I am thinking of whenever a month is filtered to create a dummy table that group sales by Shop IDs and ordered them from high to low and then assigns segments to them based on their percentage.

then in the table visual I just count the ID by segments cause it's already a distinct count of shops because it was grouped by shop id but I'm not sure how to achieve that.

[Anonymous]

Hi @Anonymous ,

As per my understanding, if I want to set date and city filters in the table, I have to add the date and city condition when calculating the total sales by shopid, is this right?please feel free to reply.

Total Sales by Shop = 
CALCULATE(
    SUM('Larger_Test_Sales_Data'[sales qty]),
    ALLEXCEPT('Larger_Test_Sales_Data', 'Larger_Test_Sales_Data'[shop ID],Larger_Test_Sales_Data[City],Larger_Test_Sales_Data[Date].[Month])
)

Best regards,

Joyce

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