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

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!




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
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.

Top Solution Authors