Forum Discussion

qwaszx55's avatar
qwaszx55
Frequent Visitor
8 years ago

Terrible at filtering.

So we have a workflow for engineers where they document their time against an opportunity, that once sold gets turned into a project, that they may then again document their time against. I'm trying to make a simple sold hours vs actual (opportunity hours + project hours) chart.

 

Our opportunity table is:

 

OppNo, ProjectNo

 

 

Our timebill table is:

 

UserId, Opportunity#, Project#, Time billed

 

The problem I feel I am running into is we have data in this table that looks like this:

 

james, (blank) , 12345, 3

Jim, 42453, (blank) 5

 

I tried to do a measure of calculate(sum(timebilled),project#=blank)) to only summerize the ones with opportunity listed but it isn't working. What is the correct forumula for this?

Need Help

2 Replies

  • stretcharm's avatar
    stretcharm
    Icon for Memorable Member rankMemorable Member
    8 years ago

    Personally I try to avoid blanks/nulls and replace them with a dummy value.

     

    I would also add a conditional column in M(Query Editor) or computed column to Set the Type e.g. Opportunity/Project

    You could also then have 2 conditional colums/computed column or measures for Opp Time Billed and Project Time Billed.

    They could be aggregated without a filter.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    8 years ago

    Hi,

     

    You only want to sum up the hours where there is a blank in the Opportunity# column.  Try this

     

    =CALCULATE(SUM(Data[Time billed]),FILTER(Data,ISBLANK(Data[Opportunity#])))

     

    Does this work?  If not, then share the link from where i can download your PBI file.