PowerBI different column

[Anonymous]

Hi,

Trying to make a stacked column chart and show the number of cases that were worked by the X person and also the number of cases that are still Open and needs to be worked by the same person.

The below is in Excel and looking to have the same (or something very similar) in PowerBI.

The problem is I have different columns and not able to combine Resolved and Open together.

When trying to take the Resolved amounts by the Case handler, I filter the State column by "Closed".

The Open amount is the opposite of Resolved. I filter the State column by everything except "Closed" (there're multiple state names, i.e Re-Opened, On Hold).

The problem in PowerBI is that on the axis I cannot use the "Assigned to" nor "Resolved by" (only here I have the names of the case handlers). Sometimes the name of the Case handler is not the same in "Assigned to" and "Resolved by"

If I put on the axis the Assigned to, the total amount of cases that are "Resolved" is not correct as someone else might have actually worked that case.

If I use the Resolved by into the axis, this will only give me the cases that are Resolved as nothing will be populated into "Resolved by" if the case is Open

Looking for some help as I'm a bit stuck atm

[Anonymous]

Hi @Anonymous,

Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[amitchandak]

@Anonymous , You have visual that is very similar,

https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA200000675?tab=Overview

But does not seem like visual problem ?

Open, close you can check this blog

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[Anonymous]

Hi @amitchandak ,

Thanks for the quick reply. The problem is not with the visualisation.

On the axis I'm using "Assigned to", therefore PowerBI counts how many tickets are in State Resolved and in State Open for George. It works for Open, however sometimes the person that got assigned the case is not the one that actually works the case (for example might be sick/in holiday).

Therefore, if I got 7 cases assigned, 4 are Open,  2 resolved by me and 1 by Mike for example, when using the Assigned To as axis, the number of Open is correct (4), Resolved will be 2, but the correct amount is 1 because only 1 I've resolved.

Also, if Mike got 2 cases assigned, and resolved 2 himself, I need the resolved amount to show 3 because he resolved 2 cases that he got assigned + 1 case that got assigned to me.

Hope it makes sense