How to do I calculate a ratio from data on two different tables?

[blm001]

Hello,

I am probably going to be annoyed with the answer due to its simplicity, but I am having a brain... lock.

I want to be able to calculate the ratio of tickets per user per country per month

Here is what I have

- A table (Ticket Data) with each individial ticket that was closed, when it was closed and from which country (Full country name) the user was from.

- A table (User data) which gives me the total number of user per country (ISO Code) per month.

- A table that has a relationship (1 to many) with the other two tables that reference country names to ISO Codes.

How do I get to "number of tickets / number of users / month"?

Thanks in advance!

[v-sihou-msft]

@blm001

How did you relate Ticket table and User table?

I assume you have TicketId, ClosedDate, User, Country columns in your Ticket table. You just need this table for your calculation. You can add YearMonth column in your calculated table:

YearMonth =
LEFT ( FORMAT ( Ticket[ClosedDate], "yyyyMMdd" ), 6 )

Then you can use ALLEXCEPT() to calculate number of tickets group on user, contry, month.

Number Of Tickets =
CALCULATE (
    COUNTA ( Ticket[TicketId] ),
    ALLEXCEPT ( Ticket, Ticket[YearMonth], Ticket[User], Ticket[Country] )
)

Regards,

[blm001]

Thanks for the prompt response.  I tried what you suggested, but I did not get the desired result. Let me try to give some more details.

Here is how the tables are related to each other.

"Ticket Data" table is related (many to 1) to the "Location names" table.

"Locations Names" is the related to the "Country_user" table (1 to many).

The Ticket Data table contains distinct ticket numbers, the user's country, closed time stamp, and about 40 other columns which are not very important for this excersise.  The only thing unique in this table is the "ticketnumber" fields.

The Location names is just a basically a reference or lookup used for different ways of spelling country names and the different varieties of codes users.  I only need to columns here; SDMCountryName (which comes from the Ticket Data table) and the ISO Code (which is what the "Country User" Tables uses.

The "Country User" table contains rows of Countries, Month (1-m-2017 formated), and number of users.

I am not sure what is meant by... "Ticket[users]" in your suggestion.

Number Of Tickets =
CALCULATE (
    COUNTA ( Ticket[TicketId] ),
    ALLEXCEPT ( Ticket, Ticket[YearMonth], Ticket[User], Ticket[Country] )
)

I guess it might be a misunderstanding so this is what I wrote.

Number Of Tickets =
CALCULATE (
COUNTA ('Ticket Data'[Ticketnumber] ),
ALLEXCEPT ( 'Ticket Data', 'Ticket Data'[Month],'Ticket Data'[iso])
)

---

---

Side note: 'Ticket Data'[iso] column is new. I created by using "Column from Examples".  I was hoping this would help. It did not seem so.

So these are results I get...

 

As you can see above, the user total does not breakdown by month.  The count of tickets column is right and the new one you suggested gives me the total for the year not month as hoped.

 If I take the date dat from the "Country_user' table, now my users are correct each month, but my count of tickets loses the month detail and there is no impact on the new measure (which I cant figure yet why there is a difference.  I have no filters on).

What I would expect is something like

Country	Month	Users	Tickets
AD	Jan	20	9
	feb	21	20
	mar	19	6
	april	18	3