Forum Discussion

SachinC's avatar
SachinC
Helper V
8 years ago
Solved

Advanced Filtering and Custom Formulae

Hi,

I have some project data in Excel that I report on using PowerBI.

Here's a snippet of the data and some information on what I wish to do:-

 

Requirement: Duration Days excluding 'Pushout'. For example, Task Title 'ABC', I want to calculate all Duration days but exclude #=2 (which is a repeat) and any occurance of 'Pushout'. So, for Task Title: ABC, 32 - 24 = 8 days. How do I write a formula to get 8 days?

 

I look forward to your response.

Thank you.

Sachin

 

IDDate of Export#Task ModeCost% CompleteTask TitleTask NameDurationStartFinishPredecessors
26004/09/20171Auto Scheduled£3,850.000%ABCABC32 daysWed 23/08/17Thu 05/10/17 
26104/09/20172Auto Scheduled£3,850.000%ABCABC32 daysWed 23/08/17Thu 05/10/17 
26204/09/20173Auto Scheduled£0.000%ABC      Pushout24 daysWed 23/08/17Mon 25/09/17 
26304/09/20174Auto Scheduled£850.000%ABC      SCOPE1 dayTue 26/09/17Tue 26/09/172
26404/09/20175Auto Scheduled£0.000%ABC      INVOICE3 daysWed 27/09/17Fri 29/09/173
26504/09/20176Auto Scheduled£1,700.000%ABC      BUILD2 daysMon 02/10/17Tue 03/10/174
26604/09/20177Auto Scheduled£450.000%ABC      TEST1 dayWed 04/10/17Wed 04/10/175
26704/09/20178Auto Scheduled£850.000%ABC      DEPLOY1 dayThu 05/10/17Thu 05/10/176
26804/09/20171Auto Scheduled£24,350.000%DVCDVC68 daysWed 23/08/17Fri 24/11/17 
26904/09/20172Auto Scheduled£24,350.000%DVCDVC68 daysWed 23/08/17Fri 24/11/17 
27004/09/20173Auto Scheduled£0.000%DVC      Pushout35 daysWed 23/08/17Tue 10/10/17 
27104/09/20174Auto Scheduled£0.000%DVC       SCOPE1 dayWed 11/10/17Wed 11/10/172
27204/09/20175Auto Scheduled£0.000%DVC       INVOICE1 dayThu 12/10/17Thu 12/10/173
27304/09/20176Auto Scheduled£21,250.000%DVC       BUILD25 daysFri 13/10/17Thu 16/11/174
27404/09/20177Auto Scheduled£2,250.000%DVC       TEST5 daysFri 17/11/17Thu 23/11/175
27504/09/20178Auto Scheduled£850.000%DVC       DEPLOY1 dayFri 24/11/17Fri 24/11/176
Need Help
  • SachinC's avatar
    SachinC
    8 years ago

    I worked in the end. Rubbish PowerBI - the visual I had didn't work, so I created a new one and it worked.  Please fix this in the next release of PowerBi as it was driving me insane! Thanks for your help.

7 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi SachinC,

     

    Try this formula please. I didn't find out what kind of characters ahead some of the "Task Name". So I used a function "Right".

    Measure =
VAR allDuration =
    CALCULATE ( SUM ( Table1[Duration.1] ), 'Table1'[#] = 1 )
VAR Pushout =
    CALCULATE (
        SUM ( Table1[Duration.1] ),
        RIGHT ( 'Table1'[Task Name], 7 ) = "Pushout"
    )
RETURN
    allDuration - Pushout

    The Duration isn't numbers. We need to transfer it first. 

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

    • SachinC's avatar
      SachinC
      Helper V
      8 years ago

      Hi Dale,

      Good suggestions - however it's not doing the trick. I need to also negate #=2 as this is a repeat for #=1 - does that make sense?

      I look forward to your response.

      Regards,

      S

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        8 years ago

        Hi SachinC,

         

        I kick the #=2 out in the formula directly. So we don't need to care #=2. What's your idea? You can try one and see the result.

         

        Best Regards!

        Dale