cancel
Showing results for
Did you mean: Frequent Visitor

Trying to filter out blank rows in a measure

Ok so I have the feeling that is is really easy but it is driving me crazy ( I will paste the really simple data table at the end of this post).

My table has three columns - Date, Nae, Hours

Want to create a percentage where I divide the count of the rows of a person by the total number of rows - that isn't an issue (see below) Now what I really want to do is for the total % for Ryan to be 40% not 20%, i.e. for the total % to NOT include the two blank rows where he didn't work. I CAN do this easily on the visalization by simply adding a filter where "Count Ryan" > 0: Easy!! See It's now dividing by 10 not 20. Now I REALLY want to do this in a measure (so I can replicate for more people) but I have tried everything I can think of (which isn't much). The following does not work but shows the kind of thing I am trying. Intuitively I think this should be really easy but it's driving me bannanas so am asking for help!!

% Ryan days removed =
VAR Count_Ryan = CALCULATE(count(Data[Name]), Data[Name] = "Ryan")
VAR Count_Team_Total = CALCULATE(count (Data[Name]), Data[Name] <> blank())

RETURN

divide(Count_Ryan,
Count_Team_Total,
0)

Anyway here is the data, nice and simple to cut and paste. Thanks in advance all!!

Date   Name   Hours
 31-Mar-21 Steve 1 30-Apr-21 Steve 2 31-May-21 Steve 3 30-Jun-21 Steve 4 31-Mar-21 Ryan 1 30-Apr-21 Ryan 2 31-May-21 Bob 3 30-Jun-21 Bob 4 31-Mar-21 Steve 1 30-Apr-21 Steve 2 31-May-21 Steve 3 30-Jun-21 Steve 4 31-Mar-21 Ryan 1 30-Apr-21 Ryan 2 31-May-21 Bob 3 30-Jun-21 Bob 4 31-Mar-21 Steve 1 30-Apr-21 Steve 2 31-May-21 Steve 3 30-Jun-21 Steve 4

2 ACCEPTED SOLUTIONS  Super User

Hi @Steveyd78 ,

Try this

Ratio =
// we know that there are 20 total rows, we know that Ryans row count is 4,
//We know that the  distinct for all dates = 4, we know that Ryans Distinct Count = 2. So we want to get rid of the Dates proportionaly that Ryan is not listed on- therefore 4-2 = 2 and 2/4 = 50%
//Therefore 20*50% becomes the denominator, while the numerator is the Ryans DistinctCount //Countrows Ryan = Calculate(COUNTROWS('HoursTable'),'HoursTable'[Name] = "Ryan")
VAR _Ryan =
CALCULATE ( COUNTROWS ( 'HoursTable' ), 'HoursTable'[Name] = "Ryan" ) //4
VAR _RyanDistinct =
CALCULATE (
DISTINCTCOUNT ( HoursTable[Date] ),
FILTER ( HoursTable, HoursTable[Name] = "Ryan" )
) //2
VAR _allRows =
COUNTROWS ( HoursTable ) //20
VAR _alldCount =
DISTINCTCOUNT ( HoursTable[Date] ) //4
VAR _ratio =
DIVIDE ( _alldCount - _RyanDistinct, _alldCount )
RETURN
DIVIDE ( _Ryan, _allrows * _ratio ) Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Proud to be a Super User!  Super User

This should work for any of the names you pick:

% of Team Hours =
VAR NameHours = SUM ( Data[Hours] )
VAR TeamHours =
CALCULATE ( SUM ( Data[Hours] ), VALUES ( Data[Date] ), ALL ( Data[Name] ) )
RETURN
DIVIDE ( NameHours, TeamHours ) 6 REPLIES 6  Super User

This should work for any of the names you pick:

% of Team Hours =
VAR NameHours = SUM ( Data[Hours] )
VAR TeamHours =
CALCULATE ( SUM ( Data[Hours] ), VALUES ( Data[Date] ), ALL ( Data[Name] ) )
RETURN
DIVIDE ( NameHours, TeamHours )  Frequent Visitor

That is just awesome - am going to work through code to work out what is going on (hopefully won't take me too long!)  Super User

To get TeamHours, I use ALL ( Data[Name] ) to get all of the names (not just the one in the current filter context) but use VALUES ( Data[Date] ) to preserve the current Date filter context.  Super User

Hi @Steveyd78 ,

Try this

Ratio =
// we know that there are 20 total rows, we know that Ryans row count is 4,
//We know that the  distinct for all dates = 4, we know that Ryans Distinct Count = 2. So we want to get rid of the Dates proportionaly that Ryan is not listed on- therefore 4-2 = 2 and 2/4 = 50%
//Therefore 20*50% becomes the denominator, while the numerator is the Ryans DistinctCount //Countrows Ryan = Calculate(COUNTROWS('HoursTable'),'HoursTable'[Name] = "Ryan")
VAR _Ryan =
CALCULATE ( COUNTROWS ( 'HoursTable' ), 'HoursTable'[Name] = "Ryan" ) //4
VAR _RyanDistinct =
CALCULATE (
DISTINCTCOUNT ( HoursTable[Date] ),
FILTER ( HoursTable, HoursTable[Name] = "Ryan" )
) //2
VAR _allRows =
COUNTROWS ( HoursTable ) //20
VAR _alldCount =
DISTINCTCOUNT ( HoursTable[Date] ) //4
VAR _ratio =
DIVIDE ( _alldCount - _RyanDistinct, _alldCount )
RETURN
DIVIDE ( _Ryan, _allrows * _ratio ) Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Proud to be a Super User! Frequent Visitor

That's amazing, really appreciated the comments - will definately help me work through the code!  Super User

Hi @Steveyd78 ,

You are welcome, hope it works for you!

Nathaniel

Proud to be a Super User!  