Counting item occurence and cumultive occurence

[Krummy]

Hello everyone,

I am quite new to this and was dragged head over heels into some trouble.

What I have:

The tables "Ticket" and "Session" with a one to many connection (One Ticket that can have many sessions, but always minimum one). Both of them are several tenthousand rows big.Therefore:

The table "Ticket" stores the column  "TicketID"

The table "Session" stores the column "TicketID" and "SessionID".

(Columns saved as "text" so far, not as "number". Can be changed anytime. There are also times, so i can later calculate the engagement times)

What I want:

1.) Usage per customer

I want to know how often each customer used his ticket (= number of SessionID per TicketID, e.g. TicketID XY it used 7 Times).Therefore i guess i need a measure. But No clue how to measure that.

2.) Cumulated Amount of customers using the service of 1,2,..., n times

Final goal is a diagram which tells me "the amount of people using the service for 1,2,3,...n time(s) is equal 20".

Therefore:

x-axis: number of ticket usage (1, 2, 3,..n'th time)

y-axis: amount of users used ticket that many times

(side note: later the bar chart will be replace by a combined bar and line chart containing the averrage times spend during the 1, 2, 3..., n time of usage as line)

I guess here is also a measure or a measure column needed. But my research was not successful.

Example:

got 10 different TicketID's -> 5 Only used once (only one regarding sessionID), 3 used it twice and 2 used it thrice.

So values for x = 1 and y = 10; for x=2 and y=5 and for x=3 and y=2

(Side note:If a display like that is not possible also the following would be okay:Values for x=1 and y=5; x=2 and y = 3; x=3 and y=2)

Is that possible and if yes: how?

I tried hard, but i can't create correctly the needed measures for that calculation.

Thank you guys very much, I really hope you can help me out! 🙂

[v-huizhn-msft · Accepted solution]

Hi @Krummy,

Because there is a one to many connection between "Ticket" and "Session". In "Ticket" table, you can create a calculated column using the formula:

usage per customer=COUNT(RELATED(Session[SessionID]))

Your "Ticket" table will show as follows. In order to describe directly, I create a sample table.



Then please click the "New Table" under Modeling on home page, type the formula below.

New = SUMMARIZE(Ticket,Ticket[usage per customer],"Cumulated Amount",COUNT(Ticket[TicketID]))

You will get a new table.



Finally, please use the "New" table to create the bar chart. Select the usage oer cuatomer as X-axis level, the Cumulated Amount as Y-axis level. Please see the expected result shown in the screenshot.



Best Regards,
Angelia