cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Steveyd78
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)

Steveyd78_0-1637454171649.png

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:

Steveyd78_1-1637454286136.png

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-21Steve1
30-Apr-21Steve2
31-May-21Steve3
30-Jun-21Steve4
31-Mar-21Ryan1
30-Apr-21Ryan2
31-May-21Bob3
30-Jun-21Bob4
31-Mar-21Steve1
30-Apr-21Steve2
31-May-21Steve3
30-Jun-21Steve4
31-Mar-21Ryan1
30-Apr-21Ryan2
31-May-21Bob3
30-Jun-21Bob4
31-Mar-21Steve1
30-Apr-21Steve2
31-May-21Steve3
30-Jun-21Steve4

 

 

2 ACCEPTED SOLUTIONS
Nathaniel_C
Super User
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 )

 

ratio.PNG


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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

AlexisOlson
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 )

 

AlexisOlson_0-1637466063839.png

View solution in original post

6 REPLIES 6
AlexisOlson
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 )

 

AlexisOlson_0-1637466063839.png

View solution in original post

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

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
Super User
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 )

 

ratio.PNG


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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

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

Hi @Steveyd78 ,

You are welcome, hope it works for you!

Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors