Power BI Webinar Part 4 - Calculations and formulas

Next, we'll guide you through the process of creating calculations using calculated tables, columns, and measures. This step is all about transforming raw data into a format that suits our analysis needs, ensuring that the information we work with in our Power BI report is tailored and effective for our objectives.

1. Calculated Tables
2. Measures

Calculated Tables 

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

Calculated tables in Power BI are tables you make by using formulas. They are not directly brought in from your data sources but are created from the data you already have in Power BI. You use them to organize, summarize, or change your data in ways that make your reports easier to understand and use. For example, you can use calculated tables to group data together, clean up your data, or link tables in new ways. They help make your data analysis and reports more powerful and customized.

Let’s walk you through how to create the Calculated tables you’ll be needing in this report:

Initially, you might need to create an additional table, see the below example:

• If you are using Salesforce as your source of truth for Closed Lost and Closed Won, then please use the following calculated table:

Content Used in Opportunities = CALCULATETABLE(SUMMARIZE(CustomerActivities, CustomerActivities[FileId], "Used in Won Opportunities", CALCULATE(DISTINCTCOUNT(Opportunity[Id]), Opportunity[StageName]="Closed Won"), "Used in Lost Opportunities", CALCULATE(DISTINCTCOUNT(Opportunity[Id]), Opportunity[StageName]="Closed Lost")))

• If you are using QorusDocs as your source of truth for Closed Lost and Closed Won, then please use the following calculated table.

Content Used in Pursuits = CALCULATETABLE(SUMMARIZE(CustomerActivities, CustomerActivities[FileId], "Used in Won Pursuits", CALCULATE(DISTINCTCOUNT(CustomerActivities[PursuitId]), Pursuits[Outcome]="Won"), "Used in Lost Pursuits", CALCULATE(DISTINCTCOUNT(CustomerActivities[PursuitId]), Pursuits[Outcome]="Lost")))

Measures

We won't delve into every calculation and measure immediately. Our focus will shift to these details when it's time to construct visuals, charts, and tables. Among the measures developed are those to assess active rates, comparing the number of active versus inactive users. Such calculations are integral to our reports, underpinning the visualizations we create.

Let's jump into Part 5 - Visuals to explore the measures needed for our visuals.