Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

probably simple calculate problem

I want to make a calculated column which will give me a subtotal of one column devided by another column, based on two other columns as filters. 

 

I have a data set like this that covers 13 months. I need to add each months data manually to an excel sheet so I'm trying to minimise how much I have to touch the excel file each month. 

 

PeriodAgent GroupNodeCompleted OrdersPossible Work days
May-19FT_ISACT23244
May-19FT_ISNSW12946
May-19FT_ISQLD19788
May-19FT_ISSA10369
May-19FT_ISVIC308138
May-19FT_ISWA16869
May-19FT_OACT454176
May-19FT_ONSW1,056368
May-19FT_ONT13388
May-19FT_OQLD1,264396
May-19FT_ORR11569
May-19FT_OSA653230
May-19FT_OVIC2,021782
May-19FT_OWA980368
May-19FT_PTACT1722
May-19FT_PTNSW340184
May-19FT_PTQLD291154
May-19FT_PTSA14369
May-19FT_PTVIC381184
May-19FT_PTWA163115
May-19TotalTotal9,1483,659
Jun-19FT_ISACT18438
Jun-19FT_ISNSW6738
Jun-19FT_ISQLD15980
Jun-19FT_ISSA10857
Jun-19FT_ISVIC223112
Jun-19FT_ISWA17557
Jun-19FT_OACT385152
Jun-19FT_ONSW765323
Jun-19FT_ONT11295
Jun-19FT_OQLD1,181380
Jun-19FT_OSA621190
Jun-19FT_OVIC1,720608
Jun-19FT_OWA856304
Jun-19FT_PTACT2419
Jun-19FT_PTNSW304152
Jun-19FT_PTQLD251140
Jun-19FT_PTSA9257
Jun-19FT_PTVIC265152
Jun-19FT_PTWA16195
Jun-19TotalTotal7,6533,049

 

I want to create a column that returns the subtotal of the technician type for the month for the respective rows in the column. 

 

If I was writing it manually, what I'm trying to achieve is SUM(Completed Orders)/SUM(Possible Work Days), subtotalled by month and technician type. So the end result should look like this:

 

PeriodAgent GroupNodeCompleted OrdersPossible Work daysSubTotal Completed Orders Per Day
May-19FT_ISACT232442.504405286
May-19FT_ISNSW129462.504405286
May-19FT_ISQLD197882.504405286
May-19FT_ISSA103692.504405286
May-19FT_ISVIC3081382.504405286
May-19FT_ISWA168692.504405286
May-19FT_OACT4541762.90134724
May-19FT_ONSW1,0563682.90134724
May-19FT_ONT133882.90134724
May-19FT_OQLD1,2643962.90134724
May-19FT_ORR115692.90134724
May-19FT_OSA6532302.90134724
May-19FT_OVIC2,0217822.90134724
May-19FT_OWA9803682.90134724
May-19FT_PTACT17221.833791209
May-19FT_PTNSW3401841.833791209
May-19FT_PTQLD2911541.833791209
May-19FT_PTSA143691.833791209
May-19FT_PTVIC3811841.833791209
May-19FT_PTWA1631151.833791209
May-19TotalTotal9,1483,6592.500136649
Jun-19FT_ISACT184382.397905759
Jun-19FT_ISNSW67382.397905759
Jun-19FT_ISQLD159802.397905759
Jun-19FT_ISSA108572.397905759
Jun-19FT_ISVIC2231122.397905759
Jun-19FT_ISWA175572.397905759
Jun-19FT_OACT3851522.748538012
Jun-19FT_ONSW7653232.748538012
Jun-19FT_ONT112952.748538012
Jun-19FT_OQLD1,1813802.748538012
Jun-19FT_OSA6211902.748538012
Jun-19FT_OVIC1,7206082.748538012
Jun-19FT_OWA8563042.748538012
Jun-19FT_PTACT24191.783739837
Jun-19FT_PTNSW3041521.783739837
Jun-19FT_PTQLD2511401.783739837
Jun-19FT_PTSA92571.783739837
Jun-19FT_PTVIC2651521.783739837
Jun-19FT_PTWA161951.783739837
Jun-19TotalTotal7,6533,0492.51000328

 

 

Any ideas?

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    6 years ago

    Anonymous 

     

    Please try this

    Column = 
VAR ag_co= CALCULATE(sum(test[Completed Orders]),FILTER(test,test[Period]=EARLIER(test[Period])&&test[Agent Group]=EARLIER(test[Agent Group])))
VAR ag_wd = CALCULATE(sum(test[Possible Work days]),FILTER(test,test[Period]=EARLIER(test[Period])&&test[Agent Group]=EARLIER(test[Agent Group])))
return ag_co/ag_wd

     

2 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    6 years ago

    Anonymous 

     

    Please try this

    Column = 
VAR ag_co= CALCULATE(sum(test[Completed Orders]),FILTER(test,test[Period]=EARLIER(test[Period])&&test[Agent Group]=EARLIER(test[Agent Group])))
VAR ag_wd = CALCULATE(sum(test[Possible Work days]),FILTER(test,test[Period]=EARLIER(test[Period])&&test[Agent Group]=EARLIER(test[Agent Group])))
return ag_co/ag_wd

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Legend, Thanks so much!