trying to get average values in a pp pivot table

[nmss18]

Hello,
I have a pp pivot table analyzing the number of alerts that ocurred over the course of the year. I am having difficulty in trying to create one particular table.
I am analyzing a 1 years worth of data .
In my table i have the day of the week in the row labels (i.e. monday - thursday) and in the values I have the count of alert. This gives me the sum amount of alerts that ocurred on every monday, thuesday, wednesday and Thursday.
what I am trying to do is get an average value. That is I would like to see how many alerts we experienced on the average monday.
I toyed around with the edit measure in the PP field list to no avail.
Does anyone have any ideas?
Thanks,
Nathan

 

[MartinL]

Hi
What I did to create Rate of Sale was:
In the powerpivot screen
click on new measure
Enter the Table Name where your data comes from ie DataFile
Enter a random name for your new value ie TotSales
then in Formula enter

=AverageX(DataFile, DataFile[Value Sales]) / SUMX(DataFile,DataFile[Store Numeric Distribution])As you enter the Table name each time the fields will appear for you to choose
I know this does not completly answer your question but hopfully it will give you something to play with until another answer comes along

Martin

 

[masplin]

Hi nathan. I'm guessing in your date dable you have a years worth of dates in one column and number of alert for that day in the next column. You have then created a column to calc the day of the week for each day. Assuming that is correct you have created a pivot table and selected "Day of Week" as rows and "alert Count" as the values . The defualt for pivots is count which is what you seem to have, but in this case you can simply click on the down arrow next to "Count of Alert" in values book and click "value field settings" and change to average. This is regular pivot table table stuff.

Mike

 

[nmss18]

Mike,
I did what you suggest but when I do that I get a numbers that i dont understand.
For example this is the a partial view of the table:

[TABLE="width: 323"]
<tbody>[TR]
[TD]Date[/TD]
[TD]DOW[/TD]
[TD]Count of Event ID[/TD]
[/TR]
[TR]
[TD]2012-02-01[/TD]
[TD]Wednesday[/TD]
[TD="align: right"]2455[/TD]
[/TR]
[TR]
[TD]2012-02-02[/TD]
[TD]Thursday[/TD]
[TD="align: right"]2030[/TD]
[/TR]
[TR]
[TD]2012-02-03[/TD]
[TD]Friday[/TD]
[TD="align: right"]2914[/TD]
[/TR]
[TR]
[TD]2012-02-04[/TD]
[TD]Saturday[/TD]
[TD="align: right"]9880[/TD]
[/TR]
[TR]
[TD]2012-02-05[/TD]
[TD]Sunday[/TD]
[TD="align: right"]776[/TD]
[/TR]
[TR]
[TD]2012-02-06[/TD]
[TD]Monday[/TD]
[TD="align: right"]3533[/TD]
[/TR]
[TR]
[TD]2012-02-07[/TD]
[TD]Tuesday[/TD]
[TD="align: right"]3579[/TD]
[/TR]
[TR]
[TD]2012-02-08[/TD]
[TD]Wednesday[/TD]
[TD="align: right"]3492[/TD]
[/TR]
[TR]
[TD]2012-02-09[/TD]
[TD]Thursday[/TD]
[TD="align: right"]4120[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]


Now, when I edit the values to use the AVERAGE, this is what i get:
[TABLE="width: 323"]
<tbody>[TR]
[TD]Date[/TD]
[TD]DOW[/TD]
[TD]Count of Event ID[/TD]
[/TR]
[TR]
[TD]2012-02-01[/TD]
[TD]Wednesday[/TD]
[TD="align: right"]19374483.94[/TD]
[/TR]
[TR]
[TD]2012-02-02[/TD]
[TD]Thursday[/TD]
[TD="align: right"]19388186.68[/TD]
[/TR]
[TR]
[TD]2012-02-03[/TD]
[TD]Friday[/TD]
[TD="align: right"]19395337.61[/TD]
[/TR]
[TR]
[TD]2012-02-04[/TD]
[TD]Saturday[/TD]
[TD="align: right"]19404088.04[/TD]
[/TR]
[TR]
[TD]2012-02-05[/TD]
[TD]Sunday[/TD]
[TD="align: right"]19413972.28[/TD]
[/TR]
[TR]
[TD]2012-02-06[/TD]
[TD]Monday[/TD]
[TD="align: right"]19421127.54[/TD]
[/TR]
[TR]
[TD]2012-02-07[/TD]
[TD]Tuesday[/TD]
[TD="align: right"]19430967.82[/TD]
[/TR]
[TR]
[TD]2012-02-08[/TD]
[TD]Wednesday[/TD]
[TD="align: right"]19440831.33[/TD]
[/TR]
[TR]
[TD]2012-02-09[/TD]
[TD]Thursday[/TD]
[TD="align: right"]19450527.35[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

 

[masplin]

Ok got it. Since your data consists of a table with one row per event you just need to calculate the average per DOW using a measure. Above it was averaging the entries for a single day where the Event ID is a long number so actually just returned average Event IDs of that day and they are all about 19,400,000 hence the above result.

Step 1. Create a new pivot with DOW as rows
Step 2. Create a measure to count how many of each DOW you have in your data set. I put it in the Tables2. Your data is being filtered by the DOW in each row so counts how many distinct dates.

DOW Count=CALCULATE(countrows(DISTINCT(TalmonLog[DateText])))

Step 3. New Measure: Average event per DOW=CALCULATE(count(TalmonLog[Event ID])/Table2[DOW Count])

This works directly from the RAW data so you dont need to put either the count ir the DOW count in the pivot. You can of course drop the "Average Event per Dow" into the pivot you have on "MainPivot" tab, but onyl the Grand total number is useful if you change the DOW filter.

I have made an example in your sheet, but lost your email to send it back to you if this isn't clear.

Hope that is what you needed

Mike