Need to divide 2 table columns filtering Location. 1st Table in Number and 2nd Table in Text Format

[arun2001mjl]

Hello All,

Need help on below.

Table 1: Non voice contact details which is in Number format has sum of Non voice contacts as Email tickets, Chat tickets and Portal Tickets.

Table 2: Count of No of Agents.

Month is in Columns

Result: I need to divide Non voice contact details & Count of No of Agents. In Excel, iam getting correct result, but in PowerBI it is showing differently.

Attached Screenshot.

[arun2001mjl]

In Table 1 & 2, i have multiple columns other than Delivery Location, Date, count of agents, non-voice contact details. If i Unpivot columns Table 1 & 2, it will disturb other visuals too. In this case, how to proceed further?

[Icey · Accepted solution]

Hi @arun2001mjl ,

Based on your scenario, it is suggested to merge your two tables into one. Detailed steps:

1. I add one Index column in Table 1 to sort the Location column later.

2. UnPivot other columns both in Table 1 and Table 2.

3. Change column names based on your requirements and merge queries.

4. Uncheck "Enable load" of Table 2.

5. Close and Apply.

6. Sort Location column by Location Order column.

7. Create a measure.

Measure = 
DIVIDE (
    SUM ( 'Table 1'[Non voice contact details] ),
    SUM ( 'Table 1'[Count of No of Agents] )
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.