Create a fixed denominator

[jackj]

I'm struggling with a fairly straightforward issue and would love some assistance...

I have a matrix that contains the following, based on a table of order details that includes userID, date, and $ amount:

Rows = [First Purchase Week], a calculated column that looks for the first date of purchase for each userID.

Columns = [Week End], a calculated column that looks for the week ending that corresponds with each order date.

Values = [Count of UserID], a measure. "Count of UserID = DISTINCTCOUNT('warehouse OrderDetails'[UserID])"

The matrix displays values exactly as I would want them to appear.

What I would like to do, however, is fix the denominator of the values of the first purchase week, and use this total as the basis for cohort based analysis.  Literally dividing the results of each subsequent week, one by one, by the first week.  Below is a screenshot of the matrix

For example, I want to be able to calculate each week of the group whose first visit was 4/3/2021 (19 users), and show the percentage of them that returned each week - 19/19 in week 1, 13/19 in week 2, 4/19 in week 3, etc.

How can I do this?

Thanks in advance for any help here.

[Ashish_Mathur]

Hi,

See if my solution here helps - Identify Customers that Organisations can upsell or cross sell their products to.

[jackj]

Hi,

I'm not sure that this helped.  I did not see the applicability here.

Effectively, all I am trying to do is replicate something similar to an absolute cell reference in powerBI.  I want to always be able to use the count of users whose first visit week was in a certain period and fix that denominator against a count of those users who appeared in other weeks going forward.

[Ashish_Mathur · Accepted solution]

Hi,

This measure seems to work

Measure 2 = DIVIDE([Count of UserID],CALCULATE([Count of UserID],ALL('warehouse OrderDetails'[Week End])))

Hope this helps.

[jackj]

This worked perfectly!  Thank you!!

[Ashish_Mathur]

You are welcome.

[jackj]

Below is the link to the file:

https://drive.google.com/file/d/12rLuamYcEPfFGeVnlMk9Rr-J5fgy_bQn/view?usp=sharing

Thank you!

[Ashish_Mathur]

Hi,

Share the download link of your PBI file.