Forum Discussion
Calculating Percentage Between Two Date Columns
Hey,
- your formula works, on a single row level. This is how calculated columns work. Evaluate the formula row by row.This formula will be executed each time the table will be refreshed.
This will now lead to the "wrong" behavior if thsi column is used in any visual that provides "Total" rows or column, meaning the application of an aggregation function , by default "SUM" whenver more than one row has to be used to create a result.
For this reason I was mentioning to change the default summerization of the calculated column, eg from SUM to AVERAGE. This is the only possibility if you want to stick with calculated columns.
If you move to a measure, you can do the following:
- Create a measure for your first date coulumn
- Create a measure for your 2nd date coulumn
- Create a measure that uses both measures in your formula
Measures are in a way "overruling" the application of aggregation functions.
If you encouter difficulties to create measures please consider to create a pbix file that contains sample data, upload the file to onedrive or dropbox and share the link. If you a Excel file as data source for the pbix file, alos upload the Excel file.
Regards,
Tom
Super Userthis depends on the expected result, as always 😉
If you stick with a calculated column you can change the default summerization maybe to Average instead of SUM (the default)
You might consider to create a measure instead of a calculated column and use an iterator function like AVERAGEX(...) or just check if the one of the date columns has a single value using the functions HASONEVALUE and/or ISCROSSFILTERED combined with IF, then you can define the calculation that will be used instead of relying on the aggregation used for numeric columns.
Hopefully this provides you some ideas.
Regards,
Tom
I am not quite following. I don't understand why the original formula won't work. I can't use an Average, as I need to know the % between two columns, which happens to be a count of date fields. Can you please explain your response in more detail.
Tanks.
- TomMartens
Hey,
- your formula works, on a single row level. This is how calculated columns work. Evaluate the formula row by row.This formula will be executed each time the table will be refreshed.
This will now lead to the "wrong" behavior if thsi column is used in any visual that provides "Total" rows or column, meaning the application of an aggregation function , by default "SUM" whenver more than one row has to be used to create a result.
For this reason I was mentioning to change the default summerization of the calculated column, eg from SUM to AVERAGE. This is the only possibility if you want to stick with calculated columns.
If you move to a measure, you can do the following:
- Create a measure for your first date coulumn
- Create a measure for your 2nd date coulumn
- Create a measure that uses both measures in your formula
Measures are in a way "overruling" the application of aggregation functions.
If you encouter difficulties to create measures please consider to create a pbix file that contains sample data, upload the file to onedrive or dropbox and share the link. If you a Excel file as data source for the pbix file, alos upload the Excel file.
Regards,
Tom
Tom, thanks for the add'l suggestion. I did, however, come across this one Measure without creating all three measures as suggested. It appears to work. Here is the measure I created to divide the two columns which were calculated fields:
PROD% = DIVIDE(sum('Production Data'[ON SCHEDULE])|SUM('Production Data'[Started]))