Power BI Webinar Part 5 - Visuals

The final piece of your Power BI puzzle are your visuals. Here we will explore all 5 pages and how to create the visuals needed for you to gain insights into all the data we have spent the last 4 articles preparing. Let's get into it.

1. Adoption
2. License Usage
3. Content Harvest and Usage
4. CRM Pursuits Detailed
5. CRM Pursuits Win Rate

Adoption

In the Webinar, you can find this section at 29:24

When exploring the topic of adoption, we delve into visualizing various data points to understand how users engage with QorusDocs. This involves examining overall usage metrics, which include tracking how often searches are conducted, the creation of pursuits and assignments, and the generation and utilization of content from smart templates. Additionally, we look at user actions such as opening, previewing, inserting, and downloading content. We'll further explore the significance of these actions, from the number of searches facilitated by Auto Answer to the engagement with shared and tracked documents, offering a rounded view of adoption and usage patterns.

 Let’s begin with the “Overall Usage” chart:

1. Create and insert a chart on your page, give it the title “Overall Usage”
2. Expand the “CustomerActivities” data table
3. In the X-axis, provide “DateStamp” and check only “Year” and “Quarter”
4. In the Y-axis, provide “id”
5. The type you want here is “Count”, so click the dropdown option and select “Count (Distinct)”

Next, the difference is just in the filtering. The chart above is displaying “All Activities” whereas the next charts have their own unique filters on “Activities”. You can copy and paste the “Overall Usage” visual, as the rest of the charts will use the exact same X and Y axis. The main thing to note comes at points 3 and 4 below:

1. Copy and paste your previous visual, but rename it to “Search Usage”
2. Make sure the X and Y axes haven’t somehow changed
3. Click and drag “Activity” into the “Filter” pane
4. Set the “Activity” filter to “Search”

For the rest, we suggest you copy and paste the “Search Usage” visual, which has the correct axes and has the required “Activity” filter. See below one more example, after which you will find the filters needed for each visual:

1. Copy and paste the “Search Usage” visual, rename it to “Pursuit Creation”
2. Make sure the X and Y axes haven’t changed
3. Set the “Activity” filter to “CreatePursuit”

The “Activity” filter for the remaining visuals:

• Assignment Creation
  • CreateAssignment
• Content Created (Smart Content): Multiple selections
  • EmailCreate
  • PowerPointCreate
  • WordCreate
• Content Usage: Multiple selections
  • AddToCart
  • CopyTo
  • CreatedShare
  • DownloadedFile
  • EmailCreate
  • Insert
  • OpenFile
  • PowerPointCreate
  • Preview
  • WordCreate
• Auto Answer Searches
  • GetAnswers

Note: Each organization will have different definitions of what constitutes “Content Usage” so these options may vary. This is ultimately a guide.

Finally, we have the “Share & Track Views”. This is ever so slightly different to the rest as we are pulling our data from a different table, “ShareDocumentViews”. Let’s take a look at what is needed here, below:

1. Copy and Paste an existing visual, change the name to “Share & Track Views”
2. Expand the “ShareDocumentViews” table
3. The X-axis needs the “ViewDate” data field with just “Year” and “Quarter” checked
4. The Y-axis needs the “ViewCount” data field
5. Sum up the count
6. No filters are needed here

Back to the top

License Usage

In the Webinar, you can find this section at 33:38

The next visual concentrates on license usage, highlighting the distinction between active, inactive, and licensed users. It includes active rates, user counts, and a relative date filter to examine activity across chosen timeframes. Office and department filters, pulled from SharePoint and linked via email to QorusDocs data, enable detailed analysis. Leveraging Active Directory for current data, the visual provides insights into usage and adoption trends, demonstrating resource efficiency.

Let’s dive into the steps to create the “License Usage” page:

Active Rate:

1. Insert a “card” and place it in the top left. Title this card “Active Rate”
2. Click and drag the “Active Rate” measure you’ve created
3. If you have not yet created this measure, create it with the following formula:
   • Active Rate = [Active User] / ([Active User] + [Inactive User]

Active User:

1. Create a “card” and title it “Active User”
2. Click and drag the “Active User” measure you’ve created
3. If you have not yet created this measure, create it with the following formula:
   • Active User = DISTINCTCOUNTNOBLANK(CustomerActivities[UserId])

You repeat these steps for the next 2 cards. Here they are and the formulas needed for the measures they will use:

• Inactive User:
  • Inactive User = [Licensed Users] - [Active User]
• Licensed Users:
  • Licensed Users = COUNTROWS(FILTER(ALL(UserInfo), UserInfo[Active]=TRUE()))

Next, you have 3 “Slicers” that help you to filter the “Licensed User” page. Again, this is fairly straightforward:

• Activity Period:
  • Insert a slicer and provide the “DateStamp” data field from the “CustomerActivities” table
• Office:
  • Insert a slicer and provide the “Office” data field from the “User Information List” table (this is your Active Directory data table)
• Department:
  • Insert a slicer and provide the “Department” data field from the “User Information List” table (this is your Active Directory data table)

Now for the “Active Users” chart:

1. Select and insert a “Clustered column chart” and title it “Active Users”
2. From the “CustomerActivities” table select the “Active User” measure for the Y axis
3. From the “CustomerActivities” table select the “DateStamp” measure for the X axis, only using “Year” and “Quarter”

Copy and paste this chart for the “Overall Usage / Adoption” chart and give it that title. The only change comes on the Y axis where you will substitute “Active Users” for a count of “ID”.

And lastly, on the “License Usage” page, we have the “Details” table:

1. Select and insert the “Table” visual and title it “Details”
2. From the “CustomerActivities”, “User Information List” and “UserInfo” data tables, find and insert the following columns:
3. Role
4. DisplayName
5. First Name
6. Last Name
7. Email Address
8. Job Title
9. Department
10. Office
11. Licensed
12. Last Activity (“DateStamp” with the instruction to use “Latest”)

Note: Be wary that the filtering in your “Activity Period” slicer is not interfering with your readings.

Back to the top

Content Harvest and usage (40:48)

In the Webinar, you can find this section at 40:48

The next visual focuses on content harvest and usage, illustrating the lifecycle of content within SharePoint libraries and lists and its application in QorusDocs. It provides counts for content added, updated, used, and viewed through Share and Track, with detailed breakdowns by practice groups. Filters include location, SharePoint solutions, managed metadata columns, and practice groups, facilitating a granular analysis of content by Solutions, Practice Group, and Industry. This allows for an evaluation of the return on investment in content creation and updating. Additionally, content details feature usage metrics per content piece and a calculated win rate for content used in successful pursuits, enriched with review status, last reviewed date, next review, content owner, and industry specifics. This page serves as an insightful tool for understanding content utility and optimizing resource allocation.

Let’s jump into what this page entails and how to get it there.

Cards: Click here if unfamiliar with how to create “Cards”

Added:

• Data Table: “All Libraries Combined”
• Data Field: “FieldValuesAsTextUniqueID”
• Type: Count

Updated:

• Data Table: “All Libraries Combined”
• Data Field: Custom Measure “Was Reviewed”
• Formula: Was Reviewed = DISTINCTCOUNTNOBLANK('All Libraries Combined'[LastReviewed])

Used:

• Data Table: “Customer Activities”
• Data Field: Custom Measure “Content Usage”
• Formula: Content Usage =
• var contentusage = if(OR(OR(OR(OR(OR(OR(OR(OR(OR(CustomerActivities[Activity]="AddToCart", CustomerActivities[Activity]="CopyTo"),CustomerActivities[Activity]="DownloadFile"),CustomerActivities[Activity]="Insert"),CustomerActivities[Activity]="OpenFile"),CustomerActivities[Activity]="Preview"),CustomerActivities[Activity]="PowerPointCreate"),CustomerActivities[Activity]="WordCreate"),CustomerActivities[Activity]="EmailCreate"),CustomerActivities[Activity]="CreatedShare"), DISTINCTCOUNT(CustomerActivities[Id]), 0)

return

if(ISBLANK(contentusage),0,contentusage)

• Type: Count

Filters:

The filters used will vary between different organizations. We have used 4 Managed Metadata fields, being, “Location”, “Solution”, “Practice Group”, and “Industry”. Managed Metadata is what is used to tag your content within your SharePoint Online libraries.

With that being said, you will find the Managed Metadata columns you need in the “All Libraries Combined” table.

Note: Managed Metadata columns will end with a “.Label”.

Charts:

I am sure you are familiar with creating a Chart by now. But let’s run through the steps on the first chat. Thereafter, you will find the remainder of the charts listed below with their axes, data table and any further notes worth mentioning:

Added:

The added chart is there to inform you how many documents were added / created in which quarter. Here’s how to set it up:

1. Create a chart of your liking, and give it the Title “Added”
2. “All Libraries Combined” is the data table you will be using
3. Find the “FieldValuesAsTextUniqueID” data field, click and drag this to the “Y-axis”
4. The type here is a “Count”
5. The X-axis is the “DateCreated” data field, just use Year and Quarter

“Update” is a chart that shows you the amount of content getting reviewed and updated per quarter:

• Data table: “All Libraries Combined”
• Y-axis: “Was Reviewed” measure
  • Type: Count
• X-axis: “LastReviewed” date

“Used” tells us the amount of content that has been used per quarter:

• Data table: “CustomerActivities”
• Y-axis: “id”
  • Type: Count
• X-axis: “Date Stamp”

“Share & Track Views” is a chart that shows you the amount of views you receive per document shared:

• Data table: “SharedDocumentViews”
• Y-axis: “ViewCount”
  • Type – Sum
• X-axis: “ViewDate”

The charts below each have the exact same “Y-axis” as about, however, the “X-axis” has been substituted with a Managed Metadata column from the “All Libraries Combined” data table.

This gives you a good idea of how content is being used across different fields, in this example we used “Practice Group”. Feel free to use whichever Managed Metadata field will give you the most value, you can always change whenever you need to.

And finally, we have the “Details” table for the “Content Harvest and Usage” page:

1. Select and insert the “Table” visual and title it “Details”
2. From the “All Libraries Combined”, “Content Used in Opportunities” and “CustomerActivities” data tables, find and insert the following columns:
3. Title (ParentList.Title)
4. File Name
5. Content Link
6. Content Usage (count)
7. Win Rate
8. Status (FieldValuesAsText.OData_Moderation Status)
9. Review Status
10. Last Reviewed
11. Next Review
12. Content Owner (SME.Title)
13. Managed Metadata columns can follow

Back to the top

CRM Pursuits Detailed

In the Webinar, you can find this section at 50:30

The next visual dives into CRM pursuits in detail, showcasing win rates and the number of pursuits linked to Salesforce opportunities. It breaks down these counts by outcomes (Won, Lost, Open) and categorizes them by Solution, Industry, and Practice Group. Additionally, it features filters for Stage, Practice Group, Industry, Solutions, and Selling Points, offering a deep dive into each pursuit. This includes specific details such as Stage, Closed Date, Owner, and Practice Group, providing a clear view of CRM pursuits and their outcomes. This visualization is designed to give insights into the effectiveness of different strategies and performance across various segments.

Let’s get into what we’re working with on this page.

Cards:

Win Rate:

• Data Table: Opportunity
• Data Field: Closed Win Rate
• Formula: Closed Win Rate = [Closed Won]/([Closed Won]+[Closed Lost])
  • Closed Won = COUNTROWS(FILTER(Opportunity, Opportunity[StageName]="Closed Won"))
  • Closed Lost = COUNTROWS(FILTER(Opportunity, Opportunity[StageName]="Closed Lost"))

Pursuits:

• Data Table: Pursuits
• Data Field: ID
• Type: Count

Filters:

Again, filters will vary depending on your organization. However, we recommend you source your filters from the “Opportunity” data table. As an example, we have used “Stage”, “Practice Group”, “Industry”, “Solutions”, and “Selling Points”.

Note: If you’d like to use your Smart Fields as filters you can grab them from the “PursuitSmartField” data table.

Charts:

Once again, we’ll run through a step-by-step guide on the first chart but thereafter, you will find that the remainder of the charts are copy paste of the first “Won Lost” but have their X-axis changed. Feel free to add whatever meets your requirements, we have used “Practice Group”, “Industry”, and “Solutions” as examples.

The “Won Lost” graph gives you a visual ratio of wins vs losses, you can add the “Open” opportunities in there if you’d like, we have:

Note: If you’d like to have “Open”, find the formula under point 3.

1. Create a graph of your choice and give it the title “Won Lost”
2. Expand the “Opportunity” data table
3. Your Y-axis will have, “Closed Won”, “Closed Lost”, and “Open”, which can be found further down the data pane.
   • “Open” is a measure
   • Formula: Open = COUNTROWS(FILTER(Opportunity, Opportunity[StageName]<>"Closed Won" && Opportunity[StageName]<>"Closed Lost"))
4. Your X-axis will be your “CloseDate” date field

Lastly, we have the “Details” table for the “Content Harvest and Usage” page:

1. Select and insert the “Table” visual and title it “Details”
2. From the “Opportunity”, and “Pursuits”, data tables you can insert the following fields:
3. Name
4. Link to CRM
5. Potential Fees (Amount)
6. Stage
7. Close Date
8. Owner (from the “Pursuits” table)
9. And any further metadata you’d like to see from your “Opportunities” data table

Back to the top

CRM Pursuits Win Rate

In the Webinar, you can find this section at 54:40

The next visual zeroes in on CRM pursuits win rate, showcasing win rates alongside counts of pursuits created and linked to CRM opportunities, specifically within Salesforce. It breaks down these counts by Win Rate, Solution, Practice Group, and Industry, offering detailed insights. With filters for Opportunity, Stage, Practice Group, Industry, Solutions, and Selling Points, this visualization mirrors the previous one but focuses more on Win Rate rather than the specific outcomes of Won, Lost, and Open. This shift provides a clearer view of success rates across different dimensions, facilitating a deeper analysis of CRM pursuit effectiveness.

Cards & Filters:

From the previous page, “CRM Pursuits Detailed” copy your cards and filters and paste them here. These will be the same as before.

Charts:

Again, you can copy and paste all your charts from the previous page and paste them here. The ONLY change will be to the Y-axis.

• Remove “Closed Won”, “Closed Lost”, and “Open” from the Y-axis
• Add “Closed Win Rate”
  • “Closed Win Rate” is a measure
  • Formula: Closed Win Rate = [Closed Won]/([Closed Won]+[Closed Lost])

Back to the top

And that wraps up our series of 5 Power BI guides and the accompanying Webinar. We hope you've picked up some valuable insights along the way. If you've got any questions or need a hand with anything, don't hesitate to get in touch with your CSM or our Support team. We're all ears and here to help you get the best out of Power BI.