Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors