cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sully86
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

 

WkWorker IDDays Worker
114
125
131
143
213
225
235
245
311
325
335
345

 

 

Thanks

3 ACCEPTED SOLUTIONS
SpartaBI
Community Champion
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(
        ADDCOLUMNS(
            VALUES('Table'[Worker ID]),
            "@Total Days", CALCULATE(SUM('Table'[Days Worker]))
        ),
        [@Total Days] > 13
    )
)

 

 

SpartaBI_0-1656064906846.png

 

 


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

View solution in original post

SpartaBI
Community Champion
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 🙂

View solution in original post

SpartaBI
Community Champion
Community Champion

@Sully86 you mean like this?

 

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

 

 




2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

View solution in original post

7 REPLIES 7
v-zhangti
Community Support
Community Support

Hi, @Sully86 

 

Please try the following methods.

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

vzhangti_0-1656384880072.png

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)

vzhangti_1-1656384949120.png

 

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)

vzhangti_2-1656385030720.png

 

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)

vzhangti_3-1656385089660.png

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.

Sully86
Regular Visitor

@SpartaBI  how would I calc

>1 but < 6 days as one DAX

>6 but < 10 as another DAX

 

Thanks

SpartaBI
Community Champion
Community Champion

@Sully86 you mean like this?

 

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

 

 




2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

@SpartaBI awsome thank you 

Sully86
Regular Visitor

@SpartaBI Thank you so much 

SpartaBI
Community Champion
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 🙂

SpartaBI
Community Champion
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(
        ADDCOLUMNS(
            VALUES('Table'[Worker ID]),
            "@Total Days", CALCULATE(SUM('Table'[Days Worker]))
        ),
        [@Total Days] > 13
    )
)

 

 

SpartaBI_0-1656064906846.png

 

 


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.

Top Solution Authors