Average of Percentage Measure

[biki]

hi,

i created a report which shows average of salesman's daily visit percentage in a month

the formula to get daily percentage is simple = if(visit>1,1,visit/2), so the result must be 0, 0.5 or 1

it's valid for one level only (in this case for salesman's visit), but when it comes to higher level (eg, group leader who lead more than 1 salesman - in marketing organizational structure) the value is wrong.

here's the example:

on salesman's level, the Average column is valid, but not on group leader's. it's supposed to 74.36% which comes from total summarize of his daily percentage divided by count of the days (in this case the formula was 9.67/13) not 69.70% which i thought comes from total average of his salesmen's daily percentage (23/33).

can someone please help me how to solve this?

any help would be appreciated 

thanks so much!

[v-xicai]

Hi @biki ,

You can try to create column like DAX below.

Average= IF(HASONEVALUE(Table1[group leader]),SUMX(Table1,[Percentage])/COUNT(Table1[Visit Date]),AVERAGEX(Table1,[Percentage]))

Best Regards,

Amy

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

[biki]

hi Amy, thanks for your quick response 

i tried your suggestion, created a measure with that DAX formula, but still the monthly average of group leader's visit refers to average(all his salesman's visit in those period of dates) which has value 69.70% instead of 74.36% from sum(daily average of all his salesman's visit)/count(dates).

below i recaptured the daily salesman's visit (yellow area, only salesman did a visit), and the group leader's daily visit is come from daily average of all his salesmen (white area below dates). the valid value of group leader's monthly visit is on Valid avg column, not on Average column (pink area).

the formula on salesman X/Y/Z row is if(visit>1,1,visit/2)

the formula on group leader A row is daily average of all his salesmen

the formula on Valid avg column is sum(daily value)/count(dates) and applied for both salesman and group leader.

hope this help to understand the case 

and thanks for any help!

[v-xicai]

Hi @biki ,

So, your formula "sum(daily value)/count(dates) " doesn't work?

Could you please share your sample data screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Amy

[biki]

hi Amy,

thanks for the idea to share the sample pbix.

here it is: https://1drv.ms/u/s!As5jVvK8HsgUm3JFfCwb9TVGMrdT

i created 2 matrix tables:

1. with a column average

2. with a measure from your DAX formula

the group leader's total value shoud be 74.36% not 69.70%

thanks a bunch!

[biki]

Hi everyone,

Is there anybody, who can help me with this? I gave you the pbi example above so you can understand the case better.

Really hope there's a way around with Power Bi, thanks!