Measure Filtering issue

[Invesco]

Hi Experts

Based on the sample data below. 

crm_call_name	Date	day_name_short	week_start_date	week_end_date	month_desc_short	activity_type_1	product_name_	data_value
C027449642	23-Nov-22	Wed	21-Nov-22	27-Nov-22	Nov	f2f	Apples	1
C027449644	23-Nov-22	Wed	21-Nov-22	27-Nov-22	Nov	f2f	Apples	1
C027449744	23-Nov-22	Wed	21-Nov-22	27-Nov-22	Nov	f2f	Apples	1
C027475422	23-Nov-22	Wed	21-Nov-22	27-Nov-22	Nov	f2f	Apples	1
C027449645	23-Nov-22	Wed	21-Nov-22	27-Nov-22	Nov	f2f	Apples	1
C027475423	23-Nov-22	Wed	21-Nov-22	27-Nov-22	Nov	f2f	Apples	1
C027449641	23-Nov-22	Wed	21-Nov-22	27-Nov-22	Nov	f2f	Apples	1
C027449640	23-Nov-22	Wed	21-Nov-22	27-Nov-22	Nov	f2f	Apples	1
C027449741	23-Nov-22	Wed	21-Nov-22	27-Nov-22	Nov	f2f	Apples	1
C027449646	23-Nov-22	Wed	21-Nov-22	27-Nov-22	Nov	f2f	Trelegy	1
C027475443	24-Nov-22	Thu	21-Nov-22	27-Nov-22	Nov	f2f	Oranges	1
C027449751	24-Nov-22	Thu	21-Nov-22	27-Nov-22	Nov	f2f	Oranges	1
	21-Nov-22	Mon	21-Nov-22	27-Nov-22	Nov	customer_facing_day		1
	23-Nov-22	Wed	21-Nov-22	27-Nov-22	Nov	customer_facing_day		1
	25-Nov-22	Fri	21-Nov-22	27-Nov-22	Nov	customer_facing_day		1
	22-Nov-22	Tue	21-Nov-22	27-Nov-22	Nov	customer_facing_day		1
	24-Nov-22	Thu	21-Nov-22	27-Nov-22	Nov	customer_facing_day		1

1. John has 5 cutsomer facing days 

Measure

Days # =

    CALCULATE(SUM(act_rates_activity_volume_and_cf_days[data_value]),

            act_rates_activity_volume_and_cf_days[activity_type_1] = "customer_facing_day")+0

2. John see 12 customer f2f so 12/5 = 2.4 as his customer rate

Second measure

f2f Customers = Calculate(DISTINCTCOUNT('act_rates_activity_volume_and_cf_days'[crm_call_name]),

                            FILTER(VALUES(act_rates_activity_volume_and_cf_days),

                            not(act_rates_activity_volume_and_cf_days[activity_type_2] IN {"customer_facing_day","item_or_sample_drop","blank_call_type","Outlook_email"})))

Third Measure 

Weekly Rates =

Var Day_Count= [Days #]

Var Activity_CT= [ActivityCountTest]

Return

    Divide(Activity_CT,Day_Count,0)

Works at higher level...

When i try and filter down to product level - ie oranges  - so in our sample data john sold oranges to 2 customers - so based on customer facing days - johns rate shoud be 2/5 =0.4.....


i cannot get that using my measure - i get back 0.....not sure what the lower level filtering issue is

[v-yalanwu-msft · Accepted solution]

Hi, @Invesco ;

You could try this measure.

Weekly Rates = 
Var Day_Count= CALCULATE(SUM(act_rates_activity_volume_and_cf_days[data_value]),FILTER(ALL(act_rates_activity_volume_and_cf_days),
            act_rates_activity_volume_and_cf_days[activity_type_1] = "customer_facing_day"))
Var Activity_CT= CALCULATE(SUM(act_rates_activity_volume_and_cf_days[data_value]),
            act_rates_activity_volume_and_cf_days[activity_type_1] = "f2f")
Return
    Divide(Activity_CT,Day_Count,0)

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Invesco]

The issue is when i filter by Product_name and any other column to get lower level result - it set the customer facing days value to zero....