cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Calculate total workers where total days > than value as a %

Hi

I am trying to create a DAX to calculate how many workers have total days > than / buy total candidates

Data example below

for example, I need to count how many workers have total days worker > 15 / but total workers

 Wk Worker ID Days Worker 1 1 4 1 2 5 1 3 1 1 4 3 2 1 3 2 2 5 2 3 5 2 4 5 3 1 1 3 2 5 3 3 5 3 4 5

Thanks

3 ACCEPTED SOLUTIONS
Community Champion

@Sully86 you mean like this (I changed to 13 casue you don't have any workers there with more then 15):

``````Measure =
COUNTROWS(
FILTER(
VALUES('Table'[Worker ID]),
"@Total Days", CALCULATE(SUM('Table'[Days Worker]))
),
[@Total Days] > 13
)
)``````

Community Champion

@Sully86
my pleasure 🙂
Please don't forget to accept the previous message as a solution for community visabilty.
P.S. Check out my showcase report - got some high level stuff there. Sure you will find there a lot of cool ideas.
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up over there if you liked it 🙂

Community Champion

@Sully86 you mean like this?

``````Measure =
COUNTROWS(
FILTER(
VALUES('Table'[Worker ID]),
"@Total Days", CALCULATE(SUM('Table'[Days Worker]))
),
[@Total Days] > 1 && [@Total Days] < 6
)
)``````

7 REPLIES 7
Community Support

Hi, @Sully86

``````Total days =
CALCULATE(SUM('Table'[Days Worker]),ALLEXCEPT('Table','Table'[Worker ID]))``````

1. >= 15

``````Measure =
Var N1=CALCULATE(COUNT('Table'[Worker ID]),FILTER(ALL('Table'),[Total days]>=15))
Var N2=CALCULATE(COUNT('Table'[Worker ID]))
return
DIVIDE(N1,N2)``````

2. >1 but < 6

``````Measure 1 =
Var N1=CALCULATE(COUNT('Table'[Worker ID]),FILTER(ALL('Table'),[Total days]>1&&[Total days]<6))
Var N2=CALCULATE(COUNT('Table'[Worker ID]))
return
DIVIDE(N1,N2)``````

3. >6 but < 10

``````Measure 2 =
Var N1=CALCULATE(COUNT('Table'[Worker ID]),FILTER(ALL('Table'),[Total days]>6&&[Total days]<10))
Var N2=CALCULATE(COUNT('Table'[Worker ID]))
return
DIVIDE(N1,N2)``````

Are these the outputs you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regular Visitor

@SpartaBI  how would I calc

>1 but < 6 days as one DAX

>6 but < 10 as another DAX

Thanks

Community Champion

@Sully86 you mean like this?

``````Measure =
COUNTROWS(
FILTER(
VALUES('Table'[Worker ID]),
"@Total Days", CALCULATE(SUM('Table'[Days Worker]))
),
[@Total Days] > 1 && [@Total Days] < 6
)
)``````

Regular Visitor

@SpartaBI awsome thank you

Regular Visitor

@SpartaBI Thank you so much

Community Champion

@Sully86
my pleasure 🙂
Please don't forget to accept the previous message as a solution for community visabilty.
P.S. Check out my showcase report - got some high level stuff there. Sure you will find there a lot of cool ideas.
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up over there if you liked it 🙂

Community Champion

@Sully86 you mean like this (I changed to 13 casue you don't have any workers there with more then 15):

``````Measure =
COUNTROWS(
FILTER(
VALUES('Table'[Worker ID]),
"@Total Days", CALCULATE(SUM('Table'[Days Worker]))
),
[@Total Days] > 13
)
)``````

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors