Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to Solution.
Hey,
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:
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
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.
Hey,
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:
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]))