- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Counting item occurence and cumultive occurence

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

Krummy

Occasional Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-21-2017
12:50 PM

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!

Solved! Go to Solution.

Report Inappropriate Content

Message 1 of 2

1 ACCEPTED SOLUTION

Accepted Solutions

v-huizhn-msft

Super Contributor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-23-2017
07:27 PM

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

1 REPLY 1

v-huizhn-msft

Super Contributor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-23-2017
07:27 PM

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