Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi all,
Maybe you could help me with this one.
I'm trying to how many employees are in a certain category of average sickness (for example 0), based on the months selected in the slicer.
I have a calculation in a calculated column of the table where I calculate the percentage of each employee for each month:
If I do this for only 1 month with:
Maand | Naam/voornaam | Betaalde uren | Ziek | Percentage |
1-1-2023 | A | 168:00 | 0:00 | 0,00 |
1-1-2023 | B | 164:30 | 4:00 | 2,43 |
1-1-2023 | C | 152:30 | 0:00 | 0,00 |
1-1-2023 | D | 168:00 | 0:00 | 0,00 |
1-2-2023 | A | 168:00 | 0:00 | 0,00 |
1-2-2023 | B | 169:00 | 0:00 | 0,00 |
1-2-2023 | C | 169:00 | 22:30 | 13,31 |
1-2-2023 | D | 168:00 | 0:00 | 0,00 |
Solved! Go to Solution.
@meskens
The > 3.5 seems to be working. Not sure why you're facing issues with it. I prepared a quick sample file for testing the 3 formulas.
Aantal 0 =
SUMX (
VALUES ( '2023'[Naam/voornaam] ),
INT ( MAXX ( CALCULATETABLE ( VALUES ( '2023'[Maand] ) ), [Percentage] ) = 0 )
)
Aantal > 3.5 =
SUMX (
VALUES ( '2023'[Naam/voornaam] ),
INT ( MAXX ( CALCULATETABLE ( VALUES ( '2023'[Maand] ) ), [Percentage] ) > 3.5 )
)
Aantal 0-3.5 =
SUMX (
VALUES ( '2023'[Naam/voornaam] ),
VAR MaxPercxent =
MAXX ( CALCULATETABLE ( VALUES ( '2023'[Maand] ) ), [Percentage] )
RETURN
INT ( MaxPercxent > 0 && MaxPercxent < 3.5 )
)
Hi @tamerj1 ,
This works great!
One more question, 1 have two more categories:
>0 and <3,5
>= 3,5
However when I change youre code to
SUMX (VALUES ( '2023'[Naam/voornaam] ),INT (MAXX (CALCULATETABLE (VALUES ( '2023'[Maand] )),[Percentage]) >3.5))
It gives no number (I have more date in my own power bi) Should have been 1
When I change the code to
@meskens
The > 3.5 seems to be working. Not sure why you're facing issues with it. I prepared a quick sample file for testing the 3 formulas.
Aantal 0 =
SUMX (
VALUES ( '2023'[Naam/voornaam] ),
INT ( MAXX ( CALCULATETABLE ( VALUES ( '2023'[Maand] ) ), [Percentage] ) = 0 )
)
Aantal > 3.5 =
SUMX (
VALUES ( '2023'[Naam/voornaam] ),
INT ( MAXX ( CALCULATETABLE ( VALUES ( '2023'[Maand] ) ), [Percentage] ) > 3.5 )
)
Aantal 0-3.5 =
SUMX (
VALUES ( '2023'[Naam/voornaam] ),
VAR MaxPercxent =
MAXX ( CALCULATETABLE ( VALUES ( '2023'[Maand] ) ), [Percentage] )
RETURN
INT ( MaxPercxent > 0 && MaxPercxent < 3.5 )
)
Thanks @tamerj1 , all works now. I don't know why in first place it didn't work.
I'll look into your code to understand how it works.
Hi Tamerj1,
I get an error message : Function max only accepts a column reference as an argument.
Ok, I thought it was a column. Please try
Aantal 0 =
SUMX (
VALUES ( '2023'[Naam/voornaam] ),
INT (
MAXX (
CALCULATETABLE (
VALUES ( '2023'[Maand] )
),
[Percentage]
) = 0
)
)
Hi @meskens
Please try
Aantal 0 =
SUMX (
VALUES ( '2023'[Naam/voornaam] ),
INT ( CALCULATE ( MAX ( '2023'[Percentage] = 0 ) ) = 0 )
)
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
31 | |
15 | |
11 | |
10 | |
8 |