Weird Table Question

[dwedding3]

Hello yall!

I'm new to Powerbi, and I'm having an issue with a measure I want to make.

I have an excel table that gets data from a formstack, that has a column that basically contains a few options, along with an "Other" option that can have anything. For example:

What is your favorite color?
-Red
-Blue
-Green

-Other: [Enter Here]

So if a person's favorite color is Pink, it would be "Other: Pink" in the excel table.

Now for the hard part: I need to make a bar graph that counts up each of these values, with one very weird rule: all "Other" values must count as the same.

For example, if the table has values Red, Red, Blue, Other: Pink, Other: Yellow, Other: Orange, there would be 2 red, 1 blue, and 3 Other. 

How do I make it so all of the "others" are combined into a single value on this chart? I attempted to make a new measure that was always the color unless it started with "other" but I couldn't select the column name for some reason (It seems I need to use aggregate functions for some reason?)

Any advice?

Thanks

[DarkArchonNL]

Hi dwedding3,

One of the easier ways to achieve this is to create a calculated column in DAX, or perform the same logic in Power Query.

Basically, you create the column, let's say [Standardized Color] by using this formula in DAX:

Standardized Color =

IF(
    LEFT(YourTable[YourColumnName], 5) = "Other", 
    "Other", 
    YourTable[YourColumnName]
)

This will create an additional column. One in which all values that start with "Other", will have the value "Other", all the other values will remain as they are.

You can then use this column for your visual.

[AbhinavJoshi]

Hello @dwedding3. Just to understand the question correctly, you want all the others colours counted as one value?. As per your example, let's say you have Red, Red, Blue, Other Pink, Other: Yellow. The count should be 2 Red, 1 Blue, and 2 others. Let me know if I understand this correctly.

[dwedding3]

Yep! exactly that!

[AbhinavJoshi · Accepted solution]

Hello @dwedding3. You can achieve this Power Query Editor. Please find the full source code. I added a conditional column, it checks if your "colour column" contains "Other", it outputs other, else the column value that it contains before. Then I added index to calculate the count of colors as I have no Unique ID.

 let
Source = Excel.Workbook(File.Contents("C:\Users\AJoshi\Downloads\Random.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",1),
#"Added Conditional Column" = Table.AddColumn(#"Removed Top Rows", "Color", each if Text.Contains([Column1], "Other") then "Other" else [Column1]),
#"Renamed Columns" = Table.RenameColumns(#"Added Conditional Column",{{"Column1", "Pre_Color"}}),
#"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1, Int64.Type)
in
#"Added Index"

I hope this helps!