Problem with data filtering

[Alex1]

Hi all.

I have problem with data filtering.

My data:

Day customer first met	Day customer return	Number of customer
0	0	100
0	1	50
1	0	100
1	1	75
1	2	50
2	0	100
2	1	80
2	2	60
2	3	20

I want to calculate share of returned customer. So i divide sum of customer by day to sum of customer on 0 day. But if there is no day for customer i dont need to calculate it. For example for customer first met at day 0 there is no day 2, so when i calculate share i want to divide 110 (50+60) on 200 (100+100), but pbi divide 110 (50+60) on 300 (100+100+100). So the result is:

Day customer return	Share correct	Share in PBI
0	100%	100%
1	68%	68%
2	55%	37%
3	20%	7%

Any suggestion would be great 🙂

[Alex1 · Accepted solution]

Thx to all, two sleepless nights and i foundout how to do it. And, as always, it quet simple.

Retention share 2 =

divide(
sum(rep_ret[cou_user_ret]),
CALCULATE(
SUM(rep_ret[cou_user_ret]),
FILTER(
ALLSELECTED('rep_ret'[Cohort_day]),
'rep_ret'[Cohort_day]<=max('rep_ret'[Cohort_day])),rep_ret[Cohort lifetime]=0
)
)

Where
cou_user_ret - sum of customers
Cohort_day - day since first buy number
Cohort lifetime - activity day number 

[v-jiascu-msft]

Hi @Alex1,

Could you please share your formula? How can I get 100% in day 0?

Best Regards!

Dale

[Alex1]

No help?

[MarcelBeug]

It is rather complex for us to understand the logic. I have not the faintest idea how you got the mentioned Power BI results.

Below a suggestion in Power Query.

In query Sum, the numbers of customers are summed per "Day customer return".

In query SumDay0, a column with zeroes is added and the table is merged with itself, such that each "Day customer first met" is merged with its corresponding "Day customr return" record with value 0.

The result is grouped again to get the sums of "Number of customer"  on day 0 for each "Day customer return".

Finally both queries are merged and the sums are divided to get the share.

Query Sum:

let
    Source = Data,
    #"Grouped Rows" = Table.Group(Source, {"Day customer return"}, {{"Sum of customer", each List.Sum([Number of customer]), type number}})
in
    #"Grouped Rows"

Query SumDay0:

let
    Source = Data,
    #"Added Custom" = Table.AddColumn(Source, "Zero", each 0),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom",{"Day customer first met", "Zero"},#"Added Custom",{"Day customer first met", "Day customer return"},"Day 0",JoinKind.LeftOuter),
    #"Expanded Day 0" = Table.ExpandTableColumn(#"Merged Queries", "Day 0", {"Number of customer"}, {"Day 0.Number of customer"}),
    #"Grouped Rows" = Table.Group(#"Expanded Day 0", {"Day customer return"}, {{"Sum of day 0 customers", each List.Sum([Day 0.Number of customer]), type number}})
in
    #"Grouped Rows"

Query Share:

let
    Source = Table.NestedJoin(Sum,{"Day customer return"},SumDay0,{"Day customer return"},"SumDay0",JoinKind.LeftOuter),
    #"Expanded SumDay0" = Table.ExpandTableColumn(Source, "SumDay0", {"Sum of day 0 customers"}, {"Sum of day 0 customers"}),
    AddedPercentage = Table.AddColumn(#"Expanded SumDay0", "Share", each [Sum of customer] / [Sum of day 0 customers], Percentage.Type)
in
    AddedPercentage