Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
LmmOlsen
Regular Visitor

How to make column chart show increase or decrease?

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?

8 REPLIES 8
v-haibl-msft
Employee
Employee

@LmmOlsen

 

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

How to make column chart show increase or decrease_1.jpg

 

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
        )
    )

How to make column chart show increase or decrease_2.jpg

 

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

How to make column chart show increase or decrease_3.jpg

 

Best Regards,

Herbert

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??

@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 )
    )

How to make column chart show increase or decrease_1.jpg

 

Best Regards,

Herbert

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:
 2016-09-07 10_54_55-Book2 - Excel.png

 

Using my data I will show you my results using your formula and the results I wanted to find called Change compared with average:
2016-09-07 11_03_02-Book2 - Excel.png


Best regards Lasse Olsen

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 )
    )

@LmmOlsen

 

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

 

Best Regards,

Herbert

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...?

@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
    )
)

How to make column chart show increase or decrease_1.jpg

 

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 )
)

How to make column chart show increase or decrease_2.jpg

 

Best Regards,

Herbert

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.