cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Krummy Occasional Visitor
Occasional Visitor

Counting item occurence and cumultive occurence

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! Smiley Happy

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
v-huizhn-msft Super Contributor
Super Contributor

Re: Counting item occurence and cumultive occurence

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.

1.PNG

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.

2.PNG

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.

3.png

Best Regards,
Angelia

1 REPLY 1
Highlighted
v-huizhn-msft Super Contributor
Super Contributor

Re: Counting item occurence and cumultive occurence

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.

1.PNG

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.

2.PNG

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.

3.png

Best Regards,
Angelia