how to present the number above and below a claim without rows to base on

[Anonymous]

I have a dataset that represents training hours for each person for each month. For each month, a person must have 60 minutes of training - this means that if you have exercised for 120 minutes in January you are still above the requirement in February, if you do not have any hours this month. As a result, I do not have one row for each person in my dataset for each month.

I have used the totalytd method to present the total training for each month an presents the data in a matrix. But I want to present the number of people who are above and below the requirement for June (360 minutes) in a pie chart or similar. How can I do this when I do not have rows to count on?

TotalMinYTD = TOTALYTD(SUM(Training[TotalMin);Date[Date])

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Anonymous]

@Ashish_Mathur , so here are my tables:


     

And here are the measures I've used in the matrix:

TotalYTD = TOTALYTD(SUM(Trainings[TrainingHour]);'Date'[Date])

CheckColour = [TotalYTD]-60*(SELECTEDVALUE('Date'[MonthNr])-1)

FillColour = IF([CheckColour] >=60;2;1)

What i want now is to create a way for the user to sort the matrix into people that are over the requirement of may (360 min) and the people that are under. I also want to create a visual that shows the count of people who are over or under the requirement.

[Ashish_Mathur]

Hi,

I cannot understand your question.  Someone else who does will help you.

[smpa01]

@Anonymous  I solved this using M and still looking for a way to solve this using DAX

Started with this

Name	Date	Hours
Sam	11/1/2018	60
Sam	12/31/2018	120
Sam	2/1/2019	55
Sam	3/1/2019	185
Sam	6/1/2019	90
Peter	1/1/2006	56
Peter	2/1/2006	364

Achieved this

Name	Date	Hours	Month_Expansion	Qualifying and NonQualifying Hours
Sam	11/1/2018	60	11/30/2018	60
Sam	12/31/2018	120	12/31/2018	60
Sam	12/31/2018	120	1/31/2019	60
Sam	2/1/2019	55	2/28/2019	55
Sam	3/1/2019	185	3/31/2019	60
Sam	3/1/2019	185	4/30/2019	60
Sam	3/1/2019	185	5/31/2019	60
Sam	3/1/2019	185	6/30/2019	5
Sam	6/1/2019	90	6/30/2019	60
Sam	6/1/2019	90	7/31/2019	30
Peter	1/1/2006	56	1/31/2006	56
Peter	2/1/2006	364	2/28/2006	60
Peter	2/1/2006	364	3/31/2006	60
Peter	2/1/2006	364	4/30/2006	60
Peter	2/1/2006	364	5/31/2006	60
Peter	2/1/2006	364	6/30/2006	60
Peter	2/1/2006	364	7/31/2006	60
Peter	2/1/2006	364	8/31/2006	4

through this

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk7MVdJRMjTUN9Q3MjC0ALLNDJRideASRvrGcBlDI2QpI4gWSyDT1BRJ3BghbmiBLGGGkLCEGBSQWpJaBFIHljEwAxllhiJjhJAxNjNRio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Date = _t, Hours = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Hours", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"ad", each _, type table [Name=text, Date=date, Hours=number]}}),
#"Added Custom8" = Table.AddColumn(#"Grouped Rows", "Custom", each let
Source=[ad],
Sort=Table.Sort(Source,{{"Date", Order.Ascending}})

in
Sort),
#"Removed Other Columns1" = Table.SelectColumns(#"Added Custom8",{"Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns1", "Custom", {"Name", "Date", "Hours"}, {"Name", "Date", "Hours"}),
#"Added Custom" = Table.AddColumn(#"Expanded Custom", "Custom", each [Hours]/60),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Custom]=1 then "1" else if [Custom]<1 then "Less than 1" else "Greater than 1"),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each if Number.Mod([Custom],1)=0 then "Integer" else "Decimal"),
#"Inserted Integer-Division" = Table.AddColumn(#"Added Custom2", "Integer-Division", each Number.IntegerDivide([Custom], 1), Int64.Type),
#"Added Custom3" = Table.AddColumn(#"Inserted Integer-Division", "Custom.3", each if [Custom.2]="Integer" and [Custom.1]="1" then 1
else
if [Custom.2]="Decimal" and [Custom.1]="Less than 1" 
then 1
else if [Custom.2]="Integer" and [Custom.1]="Greater than 1" then [Custom]
else
if [Custom.2]="Decimal" and [Custom.1]="Greater than 1" then [#"Integer-Division"]+1
else 0),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom3",{"Name", "Date", "Hours", "Custom", "Custom.3"}),
#"Inserted Year" = Table.AddColumn(#"Removed Other Columns", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), Int64.Type),
#"Added Custom4" = Table.AddColumn(#"Inserted Month", "End", each if [Custom.3]=1 then #date([Year],[Month],1) else Date.AddMonths(#date([Year],[Month],1),[Custom.3])),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "Start", each #date([Year],[Month],1)),
#"Inserted Date Subtraction" = Table.AddColumn(#"Added Custom5", "Subtraction", each Duration.Days([End] - [Start]), Int64.Type),
#"Added Custom6" = Table.AddColumn(#"Inserted Date Subtraction", "Custom.1", each if [Subtraction]<>0 then List.Dates([Start],[Subtraction],#duration(1,0,0,0)) else List.Dates([Start],1,#duration(1,0,0,0))),
#"Added Custom7" = Table.AddColumn(#"Added Custom6", "Custom.2", each let
Source=[Custom.1],
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Calculated End of Month" = Table.TransformColumns(#"Converted to Table",{{"Column1", Date.EndOfMonth, type date}}),
#"Removed Duplicates" = Table.Distinct(#"Calculated End of Month"),
Column1 = #"Removed Duplicates"[Column1]
in
Column1),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom7",{"Custom.1"}),
#"Expanded Custom.2" = Table.ExpandListColumn(#"Removed Columns", "Custom.2"),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Custom.2",{{"Custom.2", "Month_Expansion"}}),
#"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 1, 1),
Custom7 = #"Inserted Integer-Division",
Custom8 = Table.AddColumn(Custom7, "Custom.3", each if [Custom.2]="Integer" and [Custom.1]="1" then 1
else
if [Custom.2]="Decimal" and [Custom.1]="Less than 1" 
then 0
else if [Custom.2]="Integer" and [Custom.1]="Greater than 1" then [Custom]
else
if [Custom.2]="Decimal" and [Custom.1]="Greater than 1" then [#"Integer-Division"]+1
else 0),
Custom1 = Table.SelectColumns(Custom8,{"Name", "Date", "Hours", "Custom", "Custom.3"}),
Custom3 = Table.AddColumn(Custom1, "Custom.1", each Number.Mod([Hours],60)),
Custom4 = Table.AddColumn(Custom3, "Custom.2", each if [Custom.3]=1 then {60} else if [Custom.3]=0 then {[Hours]} else if [Custom.3]>1 and Number.Mod([Hours],60)=0 then List.Repeat({60},[Custom.3]) else List.Repeat({60},[Custom.3]-1)),
Custom5 = Table.AddColumn(Custom4, "Remainder Hours", each if [Custom.3]=1 then "" else if [Custom.3]=0 then "" else if [Custom.3]>1 and Number.Mod([Hours],60)=0 then "" else {[Hours]-(([Custom.3]-1)*60)}),
Custom6 = Table.AddColumn(Custom5, "Custom.4", each if [Remainder Hours]="" then [Custom.2] else List.Combine({[Custom.2],[Remainder Hours]})),
#"Removed Columns1" = Table.RemoveColumns(Custom6,{"Custom.2", "Remainder Hours"}),
#"Expanded Custom.4" = Table.ExpandListColumn(#"Removed Columns1", "Custom.4"),
#"Added Index1" = Table.AddIndexColumn(#"Expanded Custom.4", "Index", 1, 1),
#"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, #"Added Index1", {"Index"}, "Added Index1", JoinKind.LeftOuter),
#"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Custom.4"}, {"Qualifying and NonQualifying Hours"}),
#"Removed Other Columns2" = Table.SelectColumns(#"Expanded Added Index1",{"Name", "Date", "Hours", "Month_Expansion", "Qualifying and NonQualifying Hours"})
in
#"Removed Other Columns2"

[Greg_Deckler]

Just divide by MONTH(Date[Date]). For June, this will be 6.

[Anonymous]

Thank you for quick respons @Greg_Deckler. But dividing the measure by six, won't give me a total number of people over and under the requirements? 

What I want is to be able to present the total number of people above and below the requirement in a pie chart. So that one can choose to sort the matrix and see only people above or below the requirement.