cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper V

## TRICKY Product Rate Measure

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.

1 ACCEPTED SOLUTION
Community Support

Hi @Invesco ,

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.

2 REPLIES 2
Community Support

Hi @Invesco ,

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.

Helper V

Matrix Table - sorry for late reply back

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.