Creating dynamic groups based on a measure

[Petanek333]

Hi, I would like to create groups of data based on a measure. To be honest, I don't even know if it is possible.

I have a table like this:

It basicaly says that in a week number 33 there were 57 units of product A (inventory) and 3 units were sold so the client has 19 weeks remaining of stock at this rate of sales. 

I have then created a calculated column with groups like Too much stock (over 18 weeks of sales), Perfect (between 8 and 18 weeks) and Running out (less than 8 weeks). 

I need to visualize it like this 

But a new request came up. They want to calculate not only the weeks remaining (inventory divided by sales) but also a "months remaining" or better put 4-week periods remaining. So I created a measure calculating the most recent week selected inventory divided by all the selected months' sales. For example if I select weeks 32, 33, 34 and 35, the formula is inventory of week 35 / (sales of weeks 32+33+34+35). 

Max Week Inventory =
CALCULATE (
    SUM ( 'Dummy Data'[Inventory Quantity] ),
    FILTER (
        ALL ( 'Dummy Data'[Year-Week] ),
        'Dummy Data'[Year-Week] = MAX ( 'Dummy Data'[Year-Week] )
    )
)

Weeks Remaining NEW =
DIVIDE (
    [Max Week Inventory],
    SUM ( 'Dummy Data'[Sales Quantity] ),
    "No sales"
)

But as the Groups are static (calculated column for each row of data) I would like to create a bar chart like the one above, but the groups Running out, Perfect and Too much should be dynamic.

So if I select 4 weeks like this:

Then the bar chart should only show a Running Out bar worth 14 389 EUR because all the weeks remaining values are below 8. 

I struggle with - connecting a table like this to a measure.

And since I don't know how to create a relationship between the table with categories and the measure, I can't try to see if it can be displayed in a graph like this. Can you help me with this? 

Sample file attached:

Sample file 

[v-jingzhang · Accepted solution]

Hi @Petanek333 

You can try this measure 

Measure = 
VAR _table = SUMMARIZE('Dummy Data','Dummy Data'[ID],"week_remaining_new",[Weeks Remaining NEW],"max_week_inventory_value",[Max Week Inventory Value])
RETURN
SWITCH(SELECTEDVALUE('Stock Category'[Type]),
    "N/A",SUMX(FILTER(_table,[week_remaining_new]<=0),[max_week_inventory_value]),
    "Running Out",SUMX(FILTER(_table,[week_remaining_new]>0 && [week_remaining_new]<=8),[max_week_inventory_value]),
    "Perfect",SUMX(FILTER(_table,[week_remaining_new]>8 && [week_remaining_new]<=18),[max_week_inventory_value]),
    "Too much",SUMX(FILTER(_table,[week_remaining_new]>18),[max_week_inventory_value])
)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Petanek333]

Hi @v-jingzhang , the measure works with this visual as expected. However, if I understand it right, those categories (Running Out, ...) are only created in a virtual table or in a measure, but is there any way to link to them in another visual?

Let's say I want to drillthough Running Out Category in this setup:

I can drillthrough for example Product C in Running Out category, but if I want to drillthrough more than one selection, I can use a trick with a card visual. 

If I click Product C and Product D holding Ctrl key, I basicaly selected the whole Running Out category and if I right-click the card visual and drillthrough, I successfully drillthrough to 10k. 

But if I click the Running Out category as a whole and then want to drillthrough via card visual, I get 14 381 EUR (thats everything for given week) and not only the Running Out category.

I think that happens because the Running Out category is only defined in that measure so my question is, is there any other way so that the drilling through would work?

Sample data 

[Petanek333]

Thank you very much @v-jingzhang , this is exactly what I was looking for.

[Petanek333]

I figured I could use a measure like this to create dynamic categories:

Category Measure =
SWITCH (
    TRUE (),
    [Weeks Remaining NEW] <= 0, "N/A",
    [Weeks Remaining NEW] <= 8, "Running Out",
    [Weeks Remaining NEW] <= 18, "Perfect",
    "Too much"
)

But since I cannot use a measure as a legend in a chart, I don't have a solution to my problem