Obtaining a sum in a measure total rather than a calculation

[Anonymous]

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:

• ThPR = Is the time that was effectively used until the first response of a ticket BEFORE it breached the SLA or "Time fo First Response Target"
• ThPRT = Is the time target to be accomplished as maximum before considering a ticket as "breached". It is a constant of 8 in any case.
• ThPRA = Is the time that is currently being ran until the ticket if first responded. It goes from ThPRT (8) down to zero.

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?

[Greg_Deckler]

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

[Anonymous]

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.