Solved: Measures don't produce an aggregated total - Page 3

Anonymous · ‎08-24-2018

Hello,

I’m trying to build a measure that gives me an understanding of the rate in which cases are resolved at the lowest level within a support organization.

I have a measure that looks at the ownership history of a case and registers a unique count if the case was ever owned by team A (lowest level).

CALCULATE ( DISTINCTCOUNT ( ‘Case’[Case Number] ), ‘Case Owner Summary’[New Value] = “Team A” )

I have a second measure doing the exact same thing for team B. Next, I want to create a “Throughput percentage”, to tell me the percentage of cases that are owned at Team A and also have to be sent to (owned by) Team B. It is possible that a case can be owned by Team B without ever being owned by Team A, in which case there should be no affect on the Throughput percentage calculation. Additionally, I don’t want to show a 0% throughput for cases that are owned at Team A, never owned at Team B, yet the case has not been moved to a ‘Closed’ status (‘Case’[Case Status] = “Closed”). However, if the case is owned by Team A and Team B and is not Closed, it should still produce a 100% throughput. I’ve been using ‘IF’ statements combined with the measures above but they produce a result that won’t allow me to aggregate the data across a range of cases; when I put them into a matrix, the throughput percentage measure reads correctly, but it will not produce a total. I have the 'show totals' ticked on, but just spinning my wheels trying to figure out a way to resolve this - any help is appreciated.

Thank you,

Ashish_Mathur · ‎08-26-2018

Hi,

Here's the measure i have written for computing the Throughput

=if(HASONEVALUE('Case'[Case Number]),if(ISBLANK([TeamBOwnershipCheck]),0,[TeamAOwnershipCheck]/[TeamBOwnershipCheck]),COUNTROWS(FILTER(CALCULATETABLE(SUMMARIZE(VALUES('Case'[Case Number]),[Case Number],"ABCD",[TeamAOwnershipCheck],"EFGH",[TeamBOwnershipCheck]),'Case'[Status]="Closed"),[ABCD]>0&&[EFGH]>0))/COUNTROWS(FILTER(SUMMARIZE(VALUES('Case'[Case Number]),[Case Number],"ABCD",[TeamAOwnershipCheck],"EFGH",[TeamBOwnershipCheck]),[ABCD]>0&&[EFGH]>0)))

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

TomMartens · ‎08-28-2018

Hey,

I guess this throughput measure will do the work:

Throughput Rate = 
AVERAGEX(
    'Case'      
    ,var currentStatus = 'Case'[Status]
    return
    IF ( [TeamAOwnershipCheck] = 1 , 
        IF ( [TeamBOwnershipCheck] = 1 
            ,1
            ,IF(currentStatus = "Open",BLANK(),0))
        , BLANK ()
    )
)

A little screenshot:

And another screenshot now filtered by Country (I copied the Country column from the sheet "Tom_Sample" to the sheet "Case":

The measures "No of closed ..." are not longer used.

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post