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
qwaszx55
Frequent Visitor

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?

2 REPLIES 2
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
stretcharm
Memorable Member
Memorable Member

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.

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.