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 have reviewed and there are a LOT of different posts similar to what I am looking for to do, so first of all: this is something that a lot of people have issues with, so I would suggest Microsoft to look into this somehow.
On second hand, I have tried multiple solutions to my problem and haven't found any way to solve it. Here is a brief of my problem:
I have a table which contains different types of "timers" (they are coming from Jira). They represent the "Time of First Response" for tickets. I am trying to obtain a total per project of this particular timer, however, the way I obtain them (that's how they come) is this:
In other words, here is the formula it should described mathematically the model:
ThPR = ThPRT - ThPRA
Now, I have designed a way to always obtain the time that effectively took the first response, not matter if it was breached or not. The logic is this one:
IF (ThPRT - ThPRA) < 0
Return ThPRTA
ELSE
Return ThPR
The problem I have (and I know this is kind of common) is that the total for my "Total of First Response" is not doing a sum of the column but a calculation like the rest of the records. By reading here and there, in this forum and a lot of other places, I came with this particular code:
_TiempoHorasHastaPrimeraRespuesta =
IF (
HASONEFILTER(Issues[Key]);
IF(
SUM ( Issues[Tiempo hasta primera respuesta Target (ms)] ) - SUM ( Issues[Tiempo hasta primera respuesta Actual (ms)] ) < 0;
SUM ( Issues[Tiempo hasta primera respuesta Actual (ms)]) / 3600000;
SUM ( Issues[Tiempo hasta primera respuesta (ms)] ) / 3600000
);
CALCULATE(
COUNTROWS(DISTINCT(Issues[Key]));
FILTER(Issues; CALCULATE(SUM ( Issues[Tiempo hasta primera respuesta Target (ms)] ) - SUM ( Issues[Tiempo hasta primera respuesta Actual (ms)] );ALLEXCEPT(Issues;Issues[Key])
)
)))
I obtain this:
Which is basically a count of records.
If I change the code to something simpler:
_TiempoHorasHastaPrimeraRespuesta =
VAR x =
SUM ( Issues[ThPRA] ) / 3600000
VAR y =
SUM ( Issues[ThPR) / 3600000
RETURN
IF (
SUM ( Issues[ThPRT] )
- SUM ( Issues[ThPRA] ) < 0;
x;
y
)
I get this:
The real result I should be obtaining should be: 109.49
I have tried everything I found and couldn't find any way to make this work.
Can you please help me out?
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Hi, I think you are on the right track. When on a summary row, group by Key and do the same calculation for each row and then sum. Combine SUMX and SUMMARIZE for that:
_TiempoHorasHastaPrimeraRespuesta =
IF (
HASONEFILTER(Issues[Key]);
IF(
SUM ( Issues[Tiempo hasta primera respuesta Target (ms)] ) - SUM ( Issues[Tiempo hasta primera respuesta Actual (ms)] ) < 0;
SUM ( Issues[Tiempo hasta primera respuesta Actual (ms)]) / 3600000;
SUM ( Issues[Tiempo hasta primera respuesta (ms)] ) / 3600000
);
SUMX(SUMMARIZE('Issues'; 'Issues'[Key]; "RowValue";
IF(
SUM ( Issues[Tiempo hasta primera respuesta Target (ms)] ) - SUM ( Issues[Tiempo hasta primera respuesta Actual (ms)] ) < 0;
SUM ( Issues[Tiempo hasta primera respuesta Actual (ms)]) / 3600000;
SUM ( Issues[Tiempo hasta primera respuesta (ms)] ) / 3600000
));
[RowValue]
))
Actually, the SUMMARIZE part works even when one row is selected, but might affect performance.
_TiempoHorasHastaPrimeraRespuesta =
SUMX(SUMMARIZE('Issues'; 'Issues'[Key]; "RowValue";
IF(
SUM ( Issues[Tiempo hasta primera respuesta Target (ms)] ) - SUM ( Issues[Tiempo hasta primera respuesta Actual (ms)] ) < 0;
SUM ( Issues[Tiempo hasta primera respuesta Actual (ms)]) / 3600000;
SUM ( Issues[Tiempo hasta primera respuesta (ms)] ) / 3600000
));
[RowValue]
)
I haven't checked parantheses etc in this formula, but the concept should work.
Edit: The Summarize is only needed if there are multiple rows with the same key. Otherwise it can solved with SUMX only.
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 |