Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Measures don't produce an aggregated total

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,

2 ACCEPTED SOLUTIONS

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.

 

Untitled.png


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

View solution in original post

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:

image.png

 

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

image.png

 

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

20 REPLIES 20
Ashish_Mathur
Super User
Super User

Hi,

 

For the same data that you share, also show the expected result.


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

Hey,

 

please create sample data, upload the pbix file to onedrive and share the link.

 

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
Anonymous
Not applicable

 

Please let me know if this helps; basically three tables and I'm using a single measure to offer a distinct count to indicate whether the case ever moves to team A, and one for team B. Then I've been using an 'If' statement to return either  0%, 100% or Blank value, which is accurate, but it does not give me the 75% reference below, which makes it uselss from a slicing standpoint. I'm guessing that the 'If' statement is where the problem resides(?). Optimistic that there's a more intelligent way to produce the desired results - please let me know if this gives you enough information. 

 

Sample Throughput Percentage.jpg

 

 

 

Hey,
please upload the excel file to onedrive or dropbox and share the link. It's simply too cumbersome to type the content from the image.

 

And I also have to admit that I still don't fully understand how the measure should work.

If a case is owned by both teams, indicated by teammembers from both teams, then what ...

 

What a bout the status Closed and Open

 

And can you please once again explain how the 75% are calculated..

 

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
Anonymous
Not applicable

Thanks for sticking with this Tom, 

 

Here's a link to a sample: https://1drv.ms/u/s!AqrYXrTAAL5Na4CriGF8DRxBsi8

Regarding the throughput calculation; I am trying to determine the rate in which cases are solved without ever going to a higher level. So in the event the case crosses into a higher level of support (Team A into Team B), we should see a 100% result, i.e 3 of 4 cases would yield a 75% throughput. Within the sample, I am only interested in measuring this against all cases that have been Closed, so there's a filter on the 'Throughput Rate' calculation. The result I would expect to see in the sample file, based on all criteria that I've mentioned, including Closed cases only, is 66% ( 4 out of 6 ), but I'm doing something wrong with the measure - hence the inability to see the total. Appreciate the help.  

 

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.

 

Untitled.png


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

Thank you so much Ashish, this works. But a question related to case nos. 28944 and 37287; is there a way to return a null value for cases that are still Open, despite only having been owned at Team A and not Team B. If not, this will still work as a solution, but thought I'd check. Appreciate your help!

Hi,

 

If my previous reply helped, please mark it as Answer.  As regards your revised question, see if this formula works

 

=if(HASONEVALUE('Case'[Case Number]),if(ISBLANK([TeamBOwnershipCheck]),BLANK(),[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)))


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

Hi Ashish, 

 

I noticed that within the two solutions that you posted:

 

The second solution, while correcting the ability to avoid returning a throughput rate for a case that has only been owned by Team A, yet is still open, works. However the new measure (Soln2) does not return a 0% throughput rate on cases that are Closed and never went to Team B (see green callouts below). 

 

The initial solution that you offered will still work - we can operate around this. The only other issue I see however is that both solutions still won't produce the correct aggregated result - see the visuals (Cards) - they should return a 0%, not a Blank. This is critical for the ability to slice (I overlooked this prior to marking as solution). I appreciate your help.  

Ashish Sample Pic.jpg

 

 

Hi,

 

In the screenshot you have shared, my first silution is showing 0% (which is what you are expecting).  So what is the problem now?


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

Hi Ashish, The problem with the first solution is that the total throughput percentage is not calculating correctly. I can use your initial solution to return the proper throughput row by row, but if I were to filter against any amount of cases, the total is not producing the correct result. For example, if I have three cases with a 100% throughput and one with a 0% throughput, I'd expect to see a combined 75% throughput. I am getting different results with the first solution you provided. 

Hi,

 

Share the PBI file which shows the problem you are alluding to.  Please also let me know the result you are expecting in that cell.


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

Hey @Anonymous,

 

I created to additional measures (sure they could also be created into your throuput measure as variables) to avoid clutter, but I also used them for testing, here is the first for Team A:

 

No Of Closed Cases Team A = 
IF(HASONEVALUE('Case'[Case Number])
    ,
    var currentCase = VALUES('Case'[Case Number])
    return
    CALCULATE(
        COUNTROWS('Case')
        ,FILTER(ALL('Case')
        ,'Case'[Status] = "Closed" && [TeamAOwnershipCheck] = 1 && 'Case'[Case Number] = currentCase)
    )
    ,CALCULATE(
        COUNTROWS('Case')
        ,FILTER(
            ALL('Case')
            ,'Case'[Status] = "Closed" && [TeamAOwnershipCheck] = 1
        )
    )
) 

And the 2nd for Team B

No Of Closed Cases Team B = 
IF(HASONEVALUE('Case'[Case Number])
    ,
    var currentCase = VALUES('Case'[Case Number])
    return
    CALCULATE(
        COUNTROWS('Case')
        ,FILTER(ALL('Case')
        ,'Case'[Status] = "Closed" && [TeamBOwnershipCheck] = 1 && 'Case'[Case Number] = currentCase)
    )
    ,CALCULATE(
        COUNTROWS('Case')
        ,FILTER(
            ALL('Case')
            ,'Case'[Status] = "Closed" && [TeamBOwnershipCheck] = 1
        )
    )
) 

 

Basically these measures reuse your measure [TeamAOwnershipCheck] so in the final Measure there will still be the blank.

 

 

And then I tweaked the measure "Throughput" a little by

 

Throughput Rate = 
IF(HASONEVALUE('Case'[Case Number]),
    IF ( [TeamAOwnershipCheck] = 1 , 
        IF ( [TeamBOwnershipCheck] = 1, 1, 0), BLANK ()
    )
    ,DIVIDE([No Of Closed Cases Team B],[No Of Closed Cases Team A],BLANK()))

 

Now considering if there is one value from the Case column, if not I added the division of the two measures I created,

But I'm wondering , if I do something wrong, because I count 6 closed cases and 5 instead of the 4 you mentioned that also involve Team A

 

Hopefully this is what you are lookfing for.

 

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
Anonymous
Not applicable

Hi Tom, 

 

Thank you very much for your help.

 

I've applied this solution, but I've noticed two things:

 

A 0% Throughput Rate is returned incorrectly in some instances - see case 37287 and 28944, which are both owned at Team A, never at Team B, yet the cases are still open, therefore they should not return any value for the througput measure (please note that this is a minor item and I can still work with the overall results apart from the second point...).

 

The second point; when filtering against any number of cases, the combined throughput rate is incorrect. Please see the Card visual below, I'd expect this to be 0%. I am attempting to compare the data across tother attributes of the case, therefore it's critical that the calculation for this piece be correct. 

 

Again, many thanks for the help, please let me know if you can think of any other ways to resolve. 

 

Tom_Sample Pic.jpg

 

 

Hey @Anonymous,

 

I'm wondering why @Ashish_Mathur and me take so long to fully meet your request, as it seems quite simple.  For this reason I ask you the following provide a simple Excel-Sheet that contains the following

case number | owned by team A | owned by team B | expected throughput

 

owned by Team ... = 1 means that your measure Check... returns 1 for that case.

Number the rows and indicate which of the rows are used for the "Total" Throughput value, also provide the expected result.

 

The Incorrect throughput values that you notice are created using the original Throughput measure.

 

Please also provide a simple recreatable example for "filtering any number of cases" in the same excel sheet mentioned above.

 

Thanks,

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
Anonymous
Not applicable

Hi Tom/Ashish,

 

First, let me state that I really do appreciate the help and I apologize if there’s some information that’s being lost in the dialogue, so let me attempt to summarize:

 

  1. The first thing that we want to identify is whether or not the case was ever owned by Team A
  2. Was the case owned by Team B?
  3. Is the case in a Closed status?

 

If the case was owned by Team A, then we want to know if it was owned by Team B as well, if so, then it should result in a 100% throughput for the case

If the case was owned by Team A, has never been owned by Team B and the case status is Closed, then it should result in a 0% throughput for the case

If the case was owned by Team A, has never been owned by Team B, however the case is not Closed, then it should not register a value for the throughput (if incorporating the case status makes it too complex, I can do without)

If the case was never owned by Team A, yet it was owned by Team B, it should not register a value for the throughput measure

 

The total throughput % that I am pursuing is essentially the average of all throughput values for all cases and it must be dynamic so that I can slice (examples in the excel file include random case attributes such as country or severity).

 

@Ashish_Mathur, the pbix file that exemplifies the issue is included, on the 'Ashish' tab, please try clicking the rows in the primary table, one by one and note the behavior of the cards to the right - they are not being calculated correctly (as an average of the throughput being evaluated within the rows that you select). The excel file may also help underscore this. 

 

Thanks again...

 

pbix: https://1drv.ms/u/s!AqrYXrTAAL5Na4CriGF8DRxBsi8

excel: https://1drv.ms/x/s!AqrYXrTAAL5NbCS7BvNX99xKKOE (see 'Tom_Sample' tab)

 

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:

image.png

 

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

image.png

 

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
Anonymous
Not applicable

@TomMartens

@Ashish_Mathur

 

Thank you both very much for your solutions, this is now working! I really, really value all the effort that you put into helping me resolve it. 

 

Regards,

You are welcome.


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

Thanks for sticking with this Tom, 

 

Here's a link to a sample: https://1drv.ms/u/s!AqrYXrTAAL5Na4CriGF8DRxBsi8

Regarding the throughput calculation; I am trying to determine the rate in which cases are solved without ever going to a higher level. So in the event the case crosses into a higher level of support (Team A into Team B), we should see a 100% result, i.e 3 of 4 cases would yield a 75% throughput. Within the sample, I am only interested in measuring this against all cases that have been Closed, so there's a filter on the 'Throughput Rate' calculation. The result I would expect to see in the sample file, based on all criteria that I've mentioned, including Closed cases only, is 66% ( 4 out of 6 ), but I'm doing something wrong with the measure - hence the inability to see the total. Appreciate the help.  

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.