Count strings in two columns and calculate ratio

[BungleBrain]

I'm trying to get from:

Ticket Raised By	Ticket Assigned To
Bob	John
Jane	Emma
Bob	Jane
John	Bob
John	Nancy
John	Emma
Jane	Emma
Edward	Jane
Emma	Jane
Emma	Bob
Bob	Emma
Bob	Jane
Bob	Nancy

To mentally something like:

Person	Count Raised	Count Assigned	Assigned/Raised Ratio
Bob	5	2	0.4
Edward	1	0	0.0
Emma	2	4	2.0
Jane	2	4	2.0
John	3	1	0.3
Nancy	0	2	Error (Infinite)

Note that a person can have a count of 0 in either column.

Is there a DAX way to get this measure so I can plot it? I've tried a few things but not getting anywhere (DAX novice). Thanks!

[amitchandak]

Created a common dimension for resources and created the following formulas with One active and one inactive join

Assign Count = CALCULATE(COUNT(Ticket[Ticket Assigned To]),USERELATIONSHIP(Assign[Ticket Assigned To],Ticket[Ticket Assigned To]))
Raised Count = CALCULATE(COUNT(Ticket[Ticket Raised By]),USERELATIONSHIP(Assign[Ticket Assigned To],Ticket[Ticket Raised By]))
Assign/Raised Ratio = DIVIDE([Assign Count],[Raised Count])

Url : https://www.dropbox.com/s/ogir49ju9w8u734/TicketAssignedResolved.pbix?dl=0

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[az38]

Hi @BungleBrain 

first, create a new table

Table Person = 
ADDCOLUMNS(
DISTINCT(UNION(SELECTCOLUMNS(Query;"Person";Query[Ticket Assigned To]);SELECTCOLUMNS(Query;"Person";Query[Ticket Raised By])));
"Count Raised";if(isblank(calculate(countrows(Query);Query[Ticket Raised By]=EARLIER([Person])));0;calculate(countrows(Query);Query[Ticket Raised By]=EARLIER([Person])));
"Count Assigned";if(isblank(calculate(countrows(Query);Query[Ticket Assigned To]=EARLIER([Person])));0;calculate(countrows(Query);Query[Ticket Assigned To]=EARLIER([Person])));

)

then add a measure

Assigned/Raised Ratio = DIVIDE([Count Assigned];[Count Raised])

but if you want to get Infinity error you can create a measure

Assigned/Raised Ratio = [Count Assigned]/[Count Raised]

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

[MartynRamsden · Accepted solution]

Hi @BungleBrain 

You'll first need to create a table of distinct names. I used the following DAX but there's likely to be a better way:

Names =
DISTINCT (
    UNION (
        ALL ( Table1[Ticket Raised By] ),
        ALL ( Table1[Ticket Assigned To] )
    )
)

Once your new table has been created, you may want to rename the column (I called it 'Name').

Then create the following measures:

Count Raised = 
VAR SelName = SELECTEDVALUE ( Names[Name] )
VAR Result = 
CALCULATE ( 
    COUNTROWS ( Table1 ),
    Table1[Ticket Raised By] = SelName
)
RETURN Result

Count Assigned = 
VAR SelName = SELECTEDVALUE ( Names[Name] )
VAR Result = 
CALCULATE ( 
    COUNTROWS ( Table1 ),
    Table1[Ticket Assigned To] = SelName
)
RETURN Result

Ratio = DIVIDE ( [Count Assigned], [Count Raised] )

When creating your table visualization in Power BI, select the column from the new table and then add your measures.

You should end up with this:

Best regards,

Martyn

If I answered your question, please help others by accepting it as a solution.