Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone,
I might be doing something wrong with my formulas as it calculates the totals as it shouldn't.
The table looks like this when I add the candidate's ID to see where the issues are (that means that each row is a different candidate):
The formula for the column in the middle is:
Solved! Go to Solution.
Have you looked at the AVERAGEX-function? It allows for a IF-statement inside the expression part, so you can do something like this
average = AVERAGEX ( 'Table'; IF ( NOT ( ISBLANK ( [measure1] ) ) && NOT ( ISBLANK ( [measure2] ) ); [measure2] - [measure1]; BLANK () ) )
Hi,
there might be something I don't understand, but in the total neither [_Average days to Interview - Scheduled] or [_Average days to Offer] is blank, so you get 38 - 76 = -38. Why should it be blank?
best regards,
hi @sturlaws
Thanks for asking,
What I was trying to do is to calculate the time between Interview Scheduled to Offer
That's why I might be wrong in calculating an average of the 2 separately and then subtract them. The only reason I did the average was to take out the 0's and have a measure available.
What I wanted to achieve with the formula below is to have a sort of calculation per Candidate ID that if they didn't pass through Interview Scheduled and Offer, to not calculate a subtraction. And as you can see in the image I attached the column is blank. But the Total is still -38.
From Interview Scheduled to Offer = IF ( ISBLANK([_Average days to Offer]) || ISBLANK([_Average days to Interview - Scheduled]), BLANK(),([_Average days to Offer] - [_Average days to Interview - Scheduled]))
I don't know how to tackle this. I'm out of ideas. What I know is that I have to add something to my formula to be based on each candidate ID.
Hope I made it more clear.
Have you looked at the AVERAGEX-function? It allows for a IF-statement inside the expression part, so you can do something like this
average = AVERAGEX ( 'Table'; IF ( NOT ( ISBLANK ( [measure1] ) ) && NOT ( ISBLANK ( [measure2] ) ); [measure2] - [measure1]; BLANK () ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |