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!

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

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.

Top Solution Authors