PowerPivot showing average instead of sum

Dear all

I'm quite new to the world of Dax and Powerpivot.

In this dataset I have the actual number of visits for sales reps in a certain district by store. This I did with a simple countrows function and it works...

I also have a theoretical # of visits. This is a measure where I calculate how many visits the sales rep is actually expected to have conducted in a certain time period.
This is calculated as a measure by multiplying the number of days selected in the pivot with the number of visits pr day expected (e.g. 30 days * 0,0328 visits/day = 0,98 visits (rounded up to 1) in a period of 30 days)

The issue occurs in the pivottable, where the subtotals for visits are summed but the subtotals for theoretical visits are avaraged... (see pictures)

Are there any quick fixes to make the pivot sum, instead of averaging, on the theoretical visits? (See attached picture)...

(Probably difficult to answer without some data...but I'll have to anonymize it before uploading, if necessary)

Thanks in advance

mr_jules2

Fair enough

I have attached a scraped down version of my file, where you might be able to see my calculations.

Again, the green data in the attached picture is behaving as wanted - the red not (obviously because im calculating something wrongly)

Thanks for any assistance!

BR

mr_jules2

Exactly

Much appreciated.

If it would help to manually input a start and end date in the excel directly...that's fine by me also... don't know if that could eliminate the tricky part...?

mr_jules2

Hi rorya

That is probably an error on my part. What I am trying to do in Days2 is simply to calculate how many days there are in the timeperiod chosen in the pivot (there is probably a smarter ways to do this) .
I suspect I have overlooked the all(bes?kskod) to the Min/max date....

I have added this to the following: StartMonth:=calculate(STARTOFMONTH(eLeaderVisit[Visitdate]);all(eLeaderVisit[COMSYS customernbr]);all(eLeaderVisit[Customername]);all(eLeaderVisit[Bes?kskod]))
Now also the kvantum store = 61, which should be the same for alle stores... Does that help?

Thanks again for your assistance!

mr_jules2

It works perfectly, as far as I can see!

Thanks a lot!