Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!
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 |
Solved! Go to 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
Proud to be a 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 )
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 )
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.
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!
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
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
20 | |
19 | |
16 | |
15 |
User | Count |
---|---|
47 | |
28 | |
25 | |
17 | |
17 |