Forum Discussion
Revenue Cohorts and Filtering
I have a revenue retention calculation which looks like this:
My problem is the denominator. (note: month group 3 is the first cohort)
I can accurately calculate the value with the following dax:
My next issue is that this doesnt take in filters for the denominator. Basically, applying a filter to "accepts marketing" will only apply to the numerator and not the denominator. I got around this with the following:
This works but is not very eligant. I also do not want to add the " __ values in (__)" filter for every possible slicer.
Is there a way to rework this to allow for external filtering but also only return the values in the first month_grouped_cohorts for each year_month?
Got it to work with the following:
var measured = calculate(sumx(orders, [order_revenue]), orders[three_month_grouped_cohorts] = 3)returnmeasured
4 Replies
- amitchandak
Super User
Ryan_S_Power_Bi , try like
first_group_revenue = CALCULATE(SUM(orders[order_revenue]),
FILTER(allselected(orders), orders[month_grouped_cohorts] = 3))Also in such case better to dimension for month_grouped_cohorts
Unfortunately, this gives me the subtotal for month_grouped_cohorts 3 in all the cells. I need the total for 3 for each corresponding first subscription month.
In other words, first sub month 2023-02-01, each group would have the values for group 3, the next row would have that total, not the subtotal of all first sub_month for group 3.
I need it to show this:
That code does this:This works for the values but does not calculate the subtotals and creates blank visuals:
first_group_revenue =var selected = SELECTEDVALUE(orders[first_sub_year_month])return CALCULATE(SUM(orders[order_revenue]),FILTER(allselected(orders), orders[month_grouped_cohorts] = 3), orders[first_sub_year_month] = selected)
