Divide each row by an aggregate of a category

[RogerSteinberg]

Hi,

I am trying to calculate the monthly retention.

I have a table that looks like this :

b1, b2 are breakdowns (e.g. country, region)

join_date: (the month a users joined)

returned_date: the month the user returned 

join_date	id	b1	b2	returned_date
Jan-19	123	xxx	yyy	Feb-19
Jan-19	456	xxx	yyy	Mar-19
Feb-19	666	xxx	yyy	Mar-19
Feb-19	666	xxx	yyy	Apr-19
Feb-19	777	xxx	yyy	Jun-19
Feb-19	888	aaa	bbb	Apr-19

I am trying to get to this:

join_date	total count joined for the month	returned_date	count of returned	rate
Jan-19	2	Feb-19	1	50%
Jan-19	2	Mar-19	1	50%
Feb-19	3	Mar-19	1	50%
Feb-19	3	Apr-19	2	66%
Feb-19	3	Jun-19	1	33%

How do I get my DAX formula to divide the current row to the total of the month . 

Thank you .

[Anonymous]

Is month the only group that needs to be considered in this calc? Like b1 and b2 aren't relevant groupings for the monthly totals? Do you have a calendar dimension with months in it?

If so:

Total Count Joined for the Month = 
DIVIDE (
COUNT ( table[id]),
CALCULATE (
  COUNT ( table[id] ),
  ALLEXCEPT( calendar[month]),
0
)

The ALLEXCEPT will ignore all other contexts and just give a sum of all items by month.

[RogerSteinberg]

b1 and b2 are relevant. Basically the breakdowns will be my slicers and the total join count will be affected by them. it should be aggreagated at the month level. i do have a calendar table.

[Anonymous · Accepted solution]

You can add b1 and b2 as conditions within the ALLEXCEPT function along with month, separated by commas.

[RogerSteinberg]

So thats the formula I wrote:

Distinct Count Join = CALCULATE(
    DISTINCTCOUNT(Query1[joined_email]),
    ALLSELECTED(
        Query1[joinDate],
        Query1[b1],
        Query1[b2],
        Query1[b3]
    )
)

Returned Count = 
CALCULATE(
    DISTINCTCOUNT(Query1[joined_email])
    )

But i get this :

joinDate	Distinct Count Joined	RecurringDateBlank	ReturnedCount	Percent
5/1/2019 0:00	16195	5/1/2019 0:00	16195	1
5/1/2019 0:00	35855	6/1/2019 0:00	35855	1
5/1/2019 0:00	30032	7/1/2019 0:00	30032	1
5/1/2019 0:00	26431	8/1/2019 0:00	26431	1
5/1/2019 0:00	22684	9/1/2019 0:00	22684	1
5/1/2019 0:00	20203	10/1/2019 0:00	20203	1
5/1/2019 0:00	18489	11/1/2019 0:00	18489	1
5/1/2019 0:00	17230	12/1/2019 0:00	17230	1
5/1/2019 0:00	15984	1/1/2020 0:00	15984	1

This distinct count joined column should remain static. the amount in the first row might confuse you but all i need is in the percent column to show 100% for that row. the Returned count column is correct all throughout.

[v-easonf-msft]

Hi , @RogerSteinberg 

Select the measure or calculated column you created and try this button "%" .

Best Regards,
Community Support Team _ Eason