Calculating Net Retention

[Anonymous]

Hi All,

Struggling to calculate Net Retention in Power BI and looking for help/suggestions. I have 2 tables to compare:

Account	Period Contract	Value
A1	Oct 1, 2015	$1,000
A2	Oct 1, 2015	$2,000
A3	Oct 1, 2015	$1,500

Account	Period Contract	Value
A1	Sept 30, 2016	$1,100
A2	Sept 30, 2016	$1,900
A3	Sept 30, 2016	$1,600
A4	Sept 30, 2016	$1,500
A5	Sept 30, 2016	$1,300

What I really want to do is compare the SUM of Contract Value between these two tables but I NEED TO FILTER FROM TABLE 2 ACCOUNTS: A4 & A5 AS THEY ARE NOT PART OF THE ORIGINAL DATASET. In SQL I would have been able to use a NOT IN type function... feel like I'm missing something simple. Any help or suggestions? 

[technolog · Accepted solution]

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.

[Baskar]

Don't worry in Dax we have multiple function to achive this .

In basically DAX join give u blank value if rows are missing .

so in your calculation u can replace the blank with whatever value u want .

let me know if u want help from my end

[Rémi]

Hello @Anonymous

I suggest you to add a calculated column into your second table =

HasNoConnection = COUNTROWS ( FILTER ( Table1 , [Account] = EARLIER ( [Account] ) ) < 1

Then you got a column that you can use in order to filter.

Rémi