cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Obtaining a sum in a measure total rather than a calculation

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:

screenshot_1.png

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:

screenshot.png

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?

2 REPLIES 2
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
UlfBergqvist
Responsive Resident
Responsive Resident

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.

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors