Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register 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 )
)
User | Count |
---|---|
16 | |
14 | |
13 | |
12 | |
11 |
User | Count |
---|---|
19 | |
16 | |
15 | |
11 | |
9 |