Stacked Column Chart with distance to monthly goal?

[pe2950]

I have a visual of a stacked column chart. 

Along my X axis i have a month start (9/1/19, 8/1/19, etc). Along my Y axis I have 3 values (Appointments kept, appointments expected, and appointments kept goal). 

Appointments Kept, and Appointments Expected are pulled from my appointments table using a relationship to a date table. My goal is stored in a goal table for each month. 

I'm trying to have the visual subtract the goal amount and display it, currently it adds the goal amount to the total. 

So for example, in this month:

Appointments Kept: 50

Appointments Expected: 10

Appointments Goal: 80

Colum displays: 50+10+80 = 140. It should instead display: 50 +10 + 20 (20 would be distance to goal)

Do i need to create a measure to perform this calculation? How can I do this when the goal data is stored in a different table compared to my appointment data?

Here is the measure for Appointments Expected:

Appt_Expected_Count_AptDate = CALCULATE(COUNT(appt[appointmentDate])+0,appt[ind_status]="Expected", USERELATIONSHIP(DateKey[Date],appt[appointmentDate]))

The measure for kept appointments is the same, just with the [ind_status] changed to "Kept"

My goals table is fairly simple, with a column for "Month Start", and then "Kept Appointment Goal" as a value, with a relationship to my DateKey table / Date *:1 Goal Table / Month_Start

Can i create a measure that takes the value from the table, and subtracts the output of two measures?

[v-lid-msft · Accepted solution]

Hi @pe2950 ,

If you want to show the three type value in one column, we can create three measure to meet your requirement.

KeptSum = CALCULATE(SUM(appt[Value]),'appt'[ind_status]="Kept")

ExpectedSum = CALCULATE(SUM(appt[Value]),'appt'[ind_status]="Expected")

diff = CALCULATE(SUM('Goal'[Goal])-SUM(appt[Value]))

If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.