cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
pe2950 Regular Visitor
Regular Visitor

Stacked Column Chart with distance to monthly goal?

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?
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Stacked Column Chart with distance to monthly goal?

Hi @pe2950 ,

 

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

 

10.PNG

 

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.

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.

View solution in original post

1 REPLY 1
Community Support Team
Community Support Team

Re: Stacked Column Chart with distance to monthly goal?

Hi @pe2950 ,

 

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

 

10.PNG

 

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.

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.

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors