Trying to filter out blank rows in a measure

[Steveyd78]

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

[Nathaniel_C · Accepted solution]

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

[AlexisOlson · Accepted solution]

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 )

[Steveyd78]

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

[AlexisOlson]

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.

[Nathaniel_C · Accepted solution]

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

[Steveyd78]

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

[Nathaniel_C]

Hi @Steveyd78 ,

You are welcome, hope it works for you!

Nathaniel