Create a new Measure - Display list of people with value <50

[Ce13]

Hi,

I have a list of people with value assigned and I would like to create a new measure to create a new list that would display the list of people for whose value is below 50. That seems really simple in excel but I am not able to find the right formula in Power BI. 

The final goal is to create a graph with % of people <50 and % of people >=50

Thanks for your help!

[Nathaniel_C · Accepted solution]

@Ce13 , sometimes it helps to visualize it to understand it, so if I took the part of the measure that was used to create the table and used the New Table button, this would be the result of what I did.  Hope that helps.

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

[Nathaniel_C]

Hi @Ce13 
Here you go,

[Ce13]

Thank you very much for your quick reply @Nathaniel_C !

I forgot to mention that in the data section, I have the list of people with multiple values (time spent).

And what I would like to see is if the sum of these values is above 50.

ex:

Bruce:20

Alex:10

Bruce: 10
Bruce:30

Alex:20

Bruce: 60 and Alex 30, so the result should be 50%.

How can I specify this parameter?

Also, when I use the % formula you provided and apply it to my case

var _above50 = CALCULATE(COUNTROWS(PI1),PI1[Time Spent (Hours)]>=150)

Var total = COUNTROWS(PI1)

return

DIVIDE(_above,total)

The syntax is not recognized for the two following elements "total" and "above".

How should I proceed?

Thank you so much for your help!!

[Nathaniel_C]

Percentage of emp greater than or equal to 50 =
VAR _newTable =
    ADDCOLUMNS (
        GROUPBY (
            myTable,
            myTable[emp],
            "TimeSpent", SUMX ( CURRENTGROUP (), [Time Spent] )
        ),
        "x", IF ( [TimeSpent] >= 50, 1 )
    )
VAR _howMany =
    SUMX ( _newTable, [x] )
VAR _percentOver =
    DIVIDE ( _howMany, DISTINCTCOUNT ( myTable[emp] ) )
RETURN
    _percentOver

Hi @Ce13 , please try this: 
Below is the pic of the data table that I used, and the result.

 
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

[Nathaniel_C · Accepted solution]

@Ce13 , sometimes it helps to visualize it to understand it, so if I took the part of the measure that was used to create the table and used the New Table button, this would be the result of what I did.  Hope that helps.

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

[Ce13]

@Nathaniel_C that works! Thank you so much!!

Is there a way I could add a condition in the formula that would do the research between two dates? then if between the two dates the sum of time entries is >=50, that would add 1 in the X column?

Here is an example of how the time is listed in of my data sources:

Alex 1h 6/8/21

Alex 0.5h 6/7/21

Bruce 2h 6/7/21

Bruce 8h 6/9/21

[Nathaniel_C]

Hi @Ce13 , yes there is.  However as I solved this, will you mark it as solved, so that others can find this quickly?  Then we can go from there.  Question do you have a calendar table in your .pbix that we create the dates?

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

[Ce13]

I marked it as a solution and added Kudos, thank you!

For now, I don't, I only have the data as follow:

[Nathaniel_C · Accepted solution]

Thank you @Ce13 , here is another solution for you:

Percentage of emp greater than or equal to 50 _1 Filtered by Date =
VAR _minDate =
    DATE ( 2021, 6, 2 ) //I hard coded these dates, but if you were doing this on a visual and had the dates inputted, then use ALLSELECTED()
VAR _maxDate =
    DATE ( 2021, 6, 5 )
VAR _myTableFiltered =
    FILTER ( myTable, myTable[Date] >= _minDate && myTable[Date] <= _maxDate )
VAR _finalCalc =
    ADDCOLUMNS (
        GROUPBY (
            _myTableFiltered,
            myTable[emp],
            "TimeSpent", SUMX ( CURRENTGROUP (), [Time Spent] )
        ),
        "x", IF ( [TimeSpent] >= 50, 1 )
    )
RETURN
    _finalCalc

My table is above.

As I say, if this is on a visual with the dates on the visual, you could use ALLSELECTED().  The crux here is to filter the table by the min and max dates, and then run the same code as before.
This was fun, thank you for posting this!
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