Calculating Net Retention

To calculate Net Retention in Power BI by comparing the sums of Contract Value from two tables and excluding specific accounts from the second table, you can follow these steps:

Create Relationships: First, ensure you have relationships established between your two tables. Typically, you'd use the 'Account' field as the common link.
Use DAX: Create a measure to calculate the sum of the Contract Value from the second table, while excluding specific accounts.
Here's an example of how you might structure your DAX measure:

AdjustedSumTable2 =
CALCULATE(
SUM('Table2'[Value]),
NOT('Table2'[Account] IN {"A4", "A5"}),
RELATEDTABLE('Table1')
)
This formula calculates the sum of the Contract Value from the second table (Table2) but excludes accounts "A4" and "A5". It also considers only those rows of Table2 that have a corresponding row in Table1 (the original dataset).

Compare Values:You can then create another measure to compute the Net Retention:
NetRetention =
AdjustedSumTable2 / SUM('Table1'[Value])
This formula computes the ratio of the adjusted sum from Table2 to the sum of Contract Value from Table1.

Visualize in Power BI:

You can now use these measures in your Power BI reports to visualize the Net Retention.
It's worth noting that while you can create separate tables for your data in Power BI, a more structured approach might be to have a single fact table with a 'Period' dimension to segregate the data by date. This would simplify many calculations and would be more in line with best practices for data modeling.