Calculating Percentage Between Two Date Columns

[Anonymous]

I wish to calculate the percentage between two date columns.   I created a New Column and used the following:

PROD % = DIVIDE('Production Data'[ON SCHEDULE]|('Production Data'[Started])

The end result looks something like this:

On Schedule         Started            Production %

    41                        84                     38.00

      3                          4                       3.00

Should I use a different formula?    Also, how do you change a New Column format to show the output as a %?

Lastly, the TOTAL row at the bottom of the table l is adding up the columns which is correct for each of the two date columns (date fields are Count by the way), however, it is also adding up the Prod %.   How do I change Prod % column to calculate the percentage between the two columns Total and NOT add up the PROD % column?

[TomMartens]

Hey,

this 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

[Anonymous]

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 · Accepted solution]

Hey,

1. 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:

1.  Create a measure for your first date coulumn
2. Create a measure for your 2nd date coulumn
3. 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

[Anonymous]

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]))