How to make column chart show increase or decrease?

[LmmOlsen]

I have a dataset pulled from Zendesk, where I have used the "(each Date.WeekOfYear([created_at])-1, type number)" function to extract the week numbers, in order to get the distinct count of ticket-ids for each week. 

I would like to make column graph on the percentage change in tickets from the previous week.

Like: 
Week 10 100tickets 
Week 11 120tickets = 20% increase
Week 12 60 tickets = 50% decrease

Any pointers on how to accomplish this?

[v-haibl-msft]

@LmmOlsen

If you already get the distinct count of ticket-ids for each week as below, I think you can try with Waterfall chart.

You need to create a column with following formula to get the increase or decrease percentage.

Change = 
VAR PreWeekTicket =
    LOOKUPVALUE ( Table1[Tickets], Table1[WeekNum], Table1[WeekNum] - 1 )
RETURN
    (
        IF (
            PreWeekTicket <> BLANK (),
            ( Table1[Tickets] - PreWeekTicket )
                / PreWeekTicket,
            1
        )
    )

Drag waterfall chart into your canvas and drag column into it as below.

Best Regards,

Herbert

[LmmOlsen]

Hey Herbert_Liu, I was able to do it as you described... However, the result only fueled my initial thoughts about showing it in this manner...

I think that it would be better to view the weekly increase/descrese in relation to the AVG number of support-tickets. But I cannot figure out how I can limit the period the Average is made over... Like the average on the past 15 weeks or so.


Something like: 

WeekVsAvg = VAR AvgTicket =
    CALCULATE(AVERAGE('Tickets (2)'[Ticket Count]);Tickets[AgeFromDateTime])
RETURN
    (
        IF (
            AvgTicket <> BLANK ();
            ( 'Tickets (2)'[Ticket Count] / AvgTicket )
               ;
            1
        )
    )

But this gives 100% in all week columns??

[v-haibl-msft]

@LmmOlsen

Please try with following formula of calculated column. I calculate the average for recent 5 weeks.

Avg Change =
VAR CurrentWeekNum =
    CALCULATE ( MAX ( Table1[WeekNum] ) )
VAR Recent5WeeksTickets =
    CALCULATE (
        SUM ( Table1[Tickets] ),
        FILTER (
            ALL ( Table1 ),
            Table1[WeekNum] <= CurrentWeekNum
                && Table1[WeekNum]
                >= CurrentWeekNum - 4
        )
    )
VAR Recent5WeeksCount =
    CALCULATE (
        DISTINCTCOUNT ( Table1[WeekNum] ),
        FILTER (
            ALL ( Table1 ),
            Table1[WeekNum] <= CurrentWeekNum
                && Table1[WeekNum]
                >= CurrentWeekNum - 4
        )
    )
VAR AvgRecent5Weeks = Recent5WeeksTickets / Recent5WeeksCount
RETURN
    (
        IF ( AvgRecent5Weeks <> BLANK (), ( Table1[Tickets] / AvgRecent5Weeks ), 1 )
    )

Best Regards,

Herbert

[LmmOlsen]

Hey Herbert, i really apprieciate your effort! But it seems that there is something wrong with the formular, and your screenshot of your test-table seems to give wrong numbers aswell. 

Your table has an average of 32 tickets over the last 5 weeks (week 20 to 15).
Dividing 32 with the last 5 weeks gives Avg Change which is different than your table... However it is the increase/decrease i'm searching for like; (("Tickets this week" / "Average Ticket Count last 5 weeks") / "Average Ticket Count last 5 weeks") Change compared with average both shown below:
 

Using my data I will show you my results using your formula and the results I wanted to find called Change compared with average:



Best regards Lasse Olsen

[LmmOlsen]

Tinkering with the code I found the reason for the numbers I dont understand...

For each row it takes the average for 10weeks backwards, like:
Week 36 makes the average for week 36-26 = 92,9 tickets
Week 35 makes the average for week 35-24 = 95,9 tickets
.....
Week 32 makes the average for week 32-23 =103,3 tickets
etc....

I want the average it uses to be the average for the latest 10 weeks from the current week.

Avg Change = 
VAR CurrentWeekNum =
    CALCULATE ( MAX ( 'Tickets (2)'[Created at WeekOfYear] ) )
VAR Recent10WeeksTickets =
    CALCULATE (
        SUM ( 'Tickets (2)'[Ticket Count] );
        FILTER (
            ALL ( 'Tickets (2)' );
             'Tickets (2)'[Created at WeekOfYear] <= CurrentWeekNum
                && 'Tickets (2)'[Created at WeekOfYear]
                >= CurrentWeekNum - 9
        )
    )
VAR Recent10WeeksCount =
    CALCULATE (
        DISTINCTCOUNT ( 'Tickets (2)'[Created at WeekOfYear] );
        FILTER (
            ALL ( 'Tickets (2)' );
            'Tickets (2)'[Created at WeekOfYear] <= CurrentWeekNum
                && 'Tickets (2)'[Created at WeekOfYear]
                >= CurrentWeekNum - 9
        )
    )
VAR AvgRecent10Weeks = Recent10WeeksTickets / Recent10WeeksCount
RETURN
    (
        IF ( AvgRecent10Weeks <> BLANK (); (( 'Tickets (2)'[Ticket Count] - AvgRecent10Weeks )/AvgRecent10Weeks); 1 )
    )

[v-haibl-msft]

@LmmOlsen

Just to confirm, have you got your expected result with the formula you posted above?

Best Regards,

Herbert

[LmmOlsen]

Hey Herbert, no the formula above gives me the average for the last 10 weeks for each week... Which changes for each week you go back.... And this gives different averages.... But I want the average (sort of "based-line") to be the last 10 weeks from the CURRENT week. A fixed number like "92,9 tickets" based on week 36-26.. 

Hope it makes sense...?

[v-haibl-msft]

@LmmOlsen

I’m not sure which average you’re looking for, so I’ll give both two average calculation method to you.

For the Moving Average Last X weeks, we can use the Column formula like below. The result will change for each week because each week has different last 5 weeks.

Moving_Avg_Last_5_Weeks = 
CALCULATE (
    AVERAGE ( Table1[Tickets] ),
    FILTER (
        Table1,
        Table1[WeekNum] <= EARLIER ( Table1[WeekNum] )
            && Table1[WeekNum]
                >= EARLIER ( Table1[WeekNum] ) - 4
    )
)

For the Average Last X weeks, we can use the Measure formula like below. The result will only change when the max week number changes (e.g. the max week number become 22 as time passed by).

Avg_Last_5_Weeks = 
CALCULATE (
    SUM ( Table1[Tickets] ),
    FILTER ( ALL ( Table1 ), Table1[WeekNum] >= MAX ( Table1[WeekNum] ) - 4 )
)

Best Regards,

Herbert