Forum Discussion
Calculations with table totals
I have these tables in PowerBI which shows the number of tasks created in a year, how long the average completiontime and when they are completed. The count and average is calculated in the visual.
The completiontime for each task is a calculated column in the database.
This is dependent on a page filter that lets you filter for the worker, department etc. via 'slicers'.
I wish to show the yearly change in percentage for the total of Created, 2y avg and 2y count (how many is completed within 2 years and how fast). E.g. from FY18 to FY19 there is a decrease of 30% in '2y avg'
Is there a method that lets you calculate the difference between 2y avg Total for FY18 and FY19 while still filtered ?
Any solution, referal to tutorials etc is greatly appreciated 😊
Hi KasperFB
Does all data come from the same source table? Do all these 4 table visuals display on the report page at the same time? If so, which filter do you set on each table visual to filter the FY?
If you want to calculate the difference between two yearly totals, you need to use measures. Assume you have a column "FY" with values FY17, FY18, FY19... in the source table, you can use below measure to get the total of created in FY19.
Created FY19 = CALCULATE ( SUM ( 'Table'[Created Number] ), ALLEXCEPT ( 'Table', 'Table'[Worker], 'Table'[Department] ), 'Table'[FY] = "FY19" )For the page filters you want to keep, you can add them to ALLEXCEPT function in above measure. You can create similar measures for other FYs. Then create the following measure to calculate the difference percentage.
Created % = DIVIDE ( [Created FY19] - [Created FY18], [Created FY18] )Or you can combine all of them into a measure
Created % = VAR vFY19 = CALCULATE ( SUM ( 'Table'[Created Number] ), ALLEXCEPT ( 'Table', 'Table'[Worker], 'Table'[Department] ), 'Table'[FY] = "FY19" ) VAR vFY18 = CALCULATE ( SUM ( 'Table'[Created Number] ), ALLEXCEPT ( 'Table', 'Table'[Worker], 'Table'[Department] ), 'Table'[FY] = "FY18" ) RETURN DIVIDE ( vFY19 - vFY18, vFY18 )Similarly, you can calculate AVERAGE and COUNT instead of SUM with the same mode.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
2 Replies
v-jingzhangCommunity Support
Hi KasperFB
Does all data come from the same source table? Do all these 4 table visuals display on the report page at the same time? If so, which filter do you set on each table visual to filter the FY?
If you want to calculate the difference between two yearly totals, you need to use measures. Assume you have a column "FY" with values FY17, FY18, FY19... in the source table, you can use below measure to get the total of created in FY19.
Created FY19 = CALCULATE ( SUM ( 'Table'[Created Number] ), ALLEXCEPT ( 'Table', 'Table'[Worker], 'Table'[Department] ), 'Table'[FY] = "FY19" )For the page filters you want to keep, you can add them to ALLEXCEPT function in above measure. You can create similar measures for other FYs. Then create the following measure to calculate the difference percentage.
Created % = DIVIDE ( [Created FY19] - [Created FY18], [Created FY18] )Or you can combine all of them into a measure
Created % = VAR vFY19 = CALCULATE ( SUM ( 'Table'[Created Number] ), ALLEXCEPT ( 'Table', 'Table'[Worker], 'Table'[Department] ), 'Table'[FY] = "FY19" ) VAR vFY18 = CALCULATE ( SUM ( 'Table'[Created Number] ), ALLEXCEPT ( 'Table', 'Table'[Worker], 'Table'[Department] ), 'Table'[FY] = "FY18" ) RETURN DIVIDE ( vFY19 - vFY18, vFY18 )Similarly, you can calculate AVERAGE and COUNT instead of SUM with the same mode.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.Thank you very much for your very detailed easy-to-understand explanation 😁
I've implemented your first solution, which works, and will now try you combined suggestion.
