TRICKY Product Rate Measure

[Anonymous]

Hi Experts 

I am trying to work out the product rate based on Business_Unit_Mapping and  Business Function and Product Name Column , based on 5 days worked per week where the acitivity_type_1 is f2f,

My Measure are

ActivityCountTest = 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_1] IN {"customer_facing_day","item_or_sample_drop","blank_call_type","Outlook_email"})))

and 

Days #=

    CALCULATE(AVERAGEX(act_rates_activity_volume_and_cf_days,act_rates_activity_volume_and_cf_days[Working Days]),

            act_rates_activity_volume_and_cf_days[activity_type_1] = "f2f")+0

then i do a divison

Weekly Rates =

Var Day_Count= [Days #]

Var Activity_CT= [ActivityCountTest]

Return

    Divide(Activity_CT,Day_Count,0)

then values are correct at employee level with the company - but i want the sub-total to show the Average of the data points, divided by the number of employees that have a value >0 i.e have made a sale

for the 21st Nov 22 i had 8 employees selling those products but only 6 made a sale so the sum of (3.3+0.8+2.0+0.4+2.4+1.2)/6 = 1.7 is correct in excel but in power bi its give me 10.5 where the except result should be 1.7

the data point (3.3+0.8+2.0+0.4+2.4+1.2) are all right error is on the sub-total line 1.7 ver 10.5 (1.7 is correct)

See sample date table below.

[v-yinliw-msft · Accepted solution]

Hi @Anonymous ,

Could you please tell me what kind of visual did you use? Table or matrix? And where did these data like 3.3, 0.8 come from? That is, I'm not sure the sales table looks like.

Could you please tell me more details about the result, I really know the logic of what you think, but I just confused with the sample data of the sales.

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Matrix Table - sorry for late reply back