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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.