Best way to show individual goals

[Caitlin_Knox]

Wondering if anyone has suggestions for me. What is the best way to show multiple calculaed columns in one visualization. They are calculated goals, specific to the individual. Id like the ability to 'sort by' and it show in some type of order and also have their names as the data labels next to the bar. Hope that makes sense. Only two are shown here, but there are actually going to be about 15 individuals

[Vish24]

Hi @Caitlin_Knox ,

Are you able to do that? I am also making similar chart. I have 15 individuals with different goals. I am trying to make a bar chart with  their achievement YTD and want to show the goals in a line on the bar chart. Could you please share if you are able to do that.

Thank  You!

[Greg_Deckler]

Perhaps put your Employee name as the Axis instead of the Legend? Not exactly sure what you are going for.

[Caitlin_Knox]

@Greg_DecklerSo each individual has a calculated column that computes their personal goal. The name of the calcluated column is the individual. I really just want the name of the calcluated column to show on the x-axis under that value

[v-ljerr-msft · Accepted solution]

Hi @Caitlin_Knox,

If I understand you correctly, you should be able to follow steps below to get your expected result.

1, Create a new table with a single column of the name of your cal column like below.

2. Use the formula below to create new measure.

Measure =
SWITCH (
    VALUES ( Table1[Name] ),
    "Total Sales", [Total Sales],
    "LY Sales", [LY Sales]
)

3. Show the measure with the "Name" column on a Chart like below.

Regards

[Caitlin_Knox]

@v-ljerr-msft@Greg_DecklerI don't understand why a second table would be required. I created a measure for each of my calcluated columns to use in a Switch statement but am getting the error that 'the expression refers to multiple columns. Multiple columns cannot be converted to a scalar value'.

Maybe if I share more of the actual logic.

I first create a calcualted column to only perform a calculation if the lookup code matches. This is repeated for 15 individuals. Each of their goal amounts/lookup codes are different.

MonthEmployee2 =
IF (
    'Opportunity Sales Report'[OwnerLookupCode] = "KUEAL1",
    DIVIDE ( 'Opportunity Sales Report'[CurrentRevenue], 4167 ),
    BLANK ()
)

Then I created a measure to create the SUM of those decimal values. The only reason I added this measure is to insert it into the Switch statement.

M Employee2 = Sum('Opportunity Sales Report'[MonthEmployee2])

So, then I'm trying to replace the name of each of these measures with just the actual employee name (without the M indicating Month)

Monthly Goals =
SWITCH (
    VALUES ( 'Opportunity Sales Report' ),
    "Employee1", [M Employee1],
    "Employee2", [M Employee2],
)

[Greg_Deckler]

Could you create a new table like:

Employee,Goal%

Employee1, #

Employee2, #

Link this table to your other table(s)? and your "Goal %" column would be the calculation of their individual goals. Then just put Employee as the Axis.

Really hard to come up with the solution without seeing how the original data is laid out.

[Caitlin_Knox]

@Greg_DecklerThe employee names you see are the names of the calcluated columns

[Greg_Deckler]

Can you give me a sense of you data? I am having trouble picturing it. Is it something like:

Column, Column 1, Employee 1, Employee 2, Employee 3, Employee 4

data,data,15%,12%,13%,11%

?

Trying to wrap my head around it.