Count from a YTD measure without all rows for the month

[Anonymous]

What i want is to create a visual that shows the count of people who are over or under the requirement (60 min for each month, so 360 min in june). I want the users to be able to select the count for "over requirements" and then see only the people over the requirement for the month in the matix. Anyone with a good advice?

So this is my matrix, and 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)

[sturlaws]

Hi @Anonymous,

nice question. To do it even better next time, add some sample data by text or link to a file in dropbox(or similar). When you post it as a picture, it means someone who wants to help you has to manually punch the numbers:
How-to-Get-Your-Question-Answered-Quickly

Now, create at new table, with 1 column and 2 rows, with "Under" and "Over" on the rows. I have called the table Requirements and the column Requirement. This table should not have relationnships to any other tables. In your Traning-table, create theese measures:

TotalYTD_noFilter = TOTALYTD(SUM(Training[TrainingHour]);dimDate[Date])

TotalYTD_over =
VAR ytd_requirement =
    [TotalYTD_noFilter]
        - 60
            * ( SELECTEDVALUE ( dimDate[Month number] ) - 1 )
RETURN
    IF ( ytd_requirement >= 60; [TotalYTD_noFilter]; BLANK () )

TotalYTD_under =
VAR ytd_requirement =
    [TotalYTD_noFilter]
        - 60
            * ( SELECTEDVALUE ( dimDate[Month number] ) - 1 )
RETURN
    IF ( ytd_requirement < 60; [TotalYTD_noFilter]; BLANK () )

TotalYTD =
IF (
    SELECTEDVALUE ( Requirement[R] ) = "Over";
    [TotalYTD_over];
    IF (
        SELECTEDVALUE ( Requirement[R] ) = "Under";
        [TotalYTD_under];
        [TotalYTD_noFilter]
    )
)

Use TotalYTD in your matrix, change the conditional formatting to Rules and add rules for FillColour=1 (as number) and FillColour=2 (as number).

Add a slicer with Requirements[Requirement].

[Anonymous]

Thank you so mutch @sturlaws ! Next time I will publish the dataset as well 🙂
But is there anyway it will be possible to count how many employees thats over the limit in a spesific month? eg. count only the people that are over 360 minutes in june

[sturlaws]

Having a bit of trouble with that. I can create a calculated table like this(in the modelling tab of power bi)

Table =
GENERATEALL (
    VALUES ( dimDate[Month number] );
    ADDCOLUMNS ( VALUES ( Training[Name] ); "TotalYTD_over"; [TotalYTD_over] )
)

and it correctly identifies the number persons over required level of training pr month. But when I try to use that piece of code inside a measure, I can't get the right count for july.

If it is acceptable to have a table as generated by the code above, it is easy to create a measure counting the number of persons. If not, I will look into it further, but will be a couple of days before I will have the time to look into it again.

[Anonymous]

Thank you so much @sturlaws !
Out of sheer curiosity and that I can probably imagine doing this at a later date also I really want to know how this can be solved - if you have time 🙂