TRICKY Product Rate Measure

Question

Hi Experts&nbsp;&nbsp;I am trying to work out the product rate based on Business_Unit_Mapping and&nbsp; Business Function and Product Name Column , based on 5 days worked per week where the acitivity_type_1 is f2f,&nbsp;My Measure areActivityCountTest = Calculate(DISTINCTCOUNT('act_rates_activity_volume_and_cf_days'[crm_call_name]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(VALUES(act_rates_activity_volume_and_cf_days), &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; not(act_rates_activity_volume_and_cf_days[activity_type_1] IN {"customer_facing_day","item_or_sample_drop","blank_call_type","Outlook_email"})))&nbsp;and&nbsp;Days #= &nbsp; &nbsp; CALCULATE(AVERAGEX(act_rates_activity_volume_and_cf_days,act_rates_activity_volume_and_cf_days[Working Days]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; act_rates_activity_volume_and_cf_days[activity_type_1] = "f2f")+0&nbsp;then i do a divisonWeekly Rates = Var Day_Count= [Days #]Var Activity_CT= [ActivityCountTest]Return&nbsp; &nbsp; 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 &gt;0 i.e have made a sale&nbsp;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&nbsp;the data point&nbsp;(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)&nbsp;&nbsp;&nbsp;See sample date table below.&nbsp;

v-yinliw-msft · Accepted Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
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.
&nbsp;
Best Regards,
Community Support Team _Yinliw
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

Forum Discussion

TRICKY Product Rate Measure

2 Replies

Related content

Power BI – YoY Measure Ignoring Product Filter but Respecting Slicers

Best Selling Product DAX measure

Production Lessons

Tricky count measure

TRICKY - SWITCH DAX MEASURE