I've set a pivot table to run from a data model so i can utilise the count distinct function.
However one of my columns won't show an average, as the format has been changed to text in the data model. Prior to changing the pivot source to the data model the average function worked.
My data is a log of information entered into a form and also tracks teh time taken to submit the form.
The time taken column is HH:MM, in the source this field is a calculated field that stats if the time is over 2 hours then leave it blank if not then use the time. Where the time is longer is longer than 2 hours its assumed the user hasn't logged out correctly but the rest of the details on the form are good, so I don't want to discount the whole row of data.
My issue with the resulting data model is the adjusted time column contains time in minutes as a decimal and also blanks "". The data model won't let me convert the adjusted time column from text due to the blanks. If i convert the blanks to 0 then my average function in the pivot table will be off as it will count the 0's. In a non data model pivot table the blanks wouldn't be included in the average.
What can i do?
However one of my columns won't show an average, as the format has been changed to text in the data model. Prior to changing the pivot source to the data model the average function worked.
My data is a log of information entered into a form and also tracks teh time taken to submit the form.
The time taken column is HH:MM, in the source this field is a calculated field that stats if the time is over 2 hours then leave it blank if not then use the time. Where the time is longer is longer than 2 hours its assumed the user hasn't logged out correctly but the rest of the details on the form are good, so I don't want to discount the whole row of data.
My issue with the resulting data model is the adjusted time column contains time in minutes as a decimal and also blanks "". The data model won't let me convert the adjusted time column from text due to the blanks. If i convert the blanks to 0 then my average function in the pivot table will be off as it will count the 0's. In a non data model pivot table the blanks wouldn't be included in the average.
What can i do?
