Making either a Calculated Column/Measure to assign data values to a category label.

[Brady-Linnebur]

Hello,

I am currently trying to get the above screenshot from excel into power bi. 

Currently I have this 

Usage Category = SWITCH(
    TRUE(),
    'Restaurants'[Usage] >= 1, "Full Usage",
    'Restaurants'[Usage] >= 0.7, "High Usage",
    'Restaurants'[Usage] >= 0.5, "Medium Usage",
    'Restaurants'[Usage] >= 0.3, "Low Usage",
    "Minimal Usage"
)

Problem I am running into is no matter what the 'Restaurants' [Usage] is, it either assigns it to Full Usage or Minimal Usage.

I have values in between however and it doesn't correctly tie them to the correct value.

Any ideas or suggestions?

[Ashish_Mathur]

Hi,

I cannot understand your question.  Share raw data in a format that can be pasted in an MS Excel file, explain the question and show the expected result.

[Brady-Linnebur]

I am unsure on what you are wanting. The 702, 497, etc. are made from a count function that goes into the [current inner open shelf usage] and sees what number it is tied to. Some numbers are over 1 like 2.7 or 8.3. So those would be in the 1 category. Anything below that would then be categorized by what their [current inner open shelf usage] is.

[Anonymous · Accepted solution]

Hi @Brady-Linnebur ,

How is the '# of stores' calculated in your table visual?

I have created sample data:

And created a calculated column:

Usage Category = SWITCH(
    TRUE(),
    'Restaurants'[Usage] >= 1, "Full Usage",
    'Restaurants'[Usage] >= 0.7, "High Usage",
    'Restaurants'[Usage] >= 0.5, "Medium Usage",
    'Restaurants'[Usage] >= 0.3, "Low Usage",
    "Minimal Usage"
)

It works fine.

There are two points to note during the operation:

1. Make sure your columns are in numeric format.

2. Remember to select "Don't summarize" when dragging it to the table visual for display, as your values will appear to be aggregated.

I would be grateful if you could provide me with your sample data in Power BI.

If you have any further questions please feel free to contact me.

pbix file is attached.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[danextian]

Hi @Brady-Linnebur ,

What is the result of this calc column in the data/table view?

[Brady-Linnebur]

Here is the output. The only thing changed since yesterday is that instead of "Full Usage", "Medium Usage" etc. I just use numbers like 1, 0.8, 0.6 etc.