Forum Discussion
variance between values in two different columns
I have data being summarized in service hour field, and then have that segmented by two separate text categories (Month) and (School Year). I would like to know the variance in the summarized Service Hour field between the same month but previous school year. Example shown below.
Hi ericbuildOn,
Based on my test, you could refer to this formula:
Variance = var a=CALCULATE(MAX('Table1'[Service Hours]),FILTER(ALL('Table1'),'Table1'[School Year]=MAX('Table1'[School Year]))) var b=CALCULATE(MIN('Table1'[Service Hours]),FILTER(ALL('Table1'),'Table1'[School Year]=MAX('Table1'[School Year]))) return a-bResult:
You can also download the PBIX file to have a view.
Regards,
Daniel He
3 Replies
Sample/example data that can be copied and pasted would be tremendously helpful. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
That being said, you should be able to create a couple VAR's that grab your MAX month and MAX year and then another VAR that does a SUMMARIZE of ALL and then some additional VAR's that use your month and year variables to extract the particular information that you want through the appropriate FILTERing. Then you just need to RETURN the calculation that you want.
Thanks for the tip and for that detailed response Greg. I'll go ahead and try that out. In the meantime, here is a sample of the data:
Region High School Service Hours Service Goal % Service Target Service Date School Year Boston High School Name 22.75 975 2.33% 1-Sep '16/'17 Boston High School Name 72.5 975 7.44% 1-Sep '17/'18 Boston High School Name 116.5 975 11.95% 2-Oct '16/'17 Boston High School Name 223.5 975 22.92% 2-Oct '17/'18 Hi ericbuildOn,
Based on my test, you could refer to this formula:
Variance = var a=CALCULATE(MAX('Table1'[Service Hours]),FILTER(ALL('Table1'),'Table1'[School Year]=MAX('Table1'[School Year]))) var b=CALCULATE(MIN('Table1'[Service Hours]),FILTER(ALL('Table1'),'Table1'[School Year]=MAX('Table1'[School Year]))) return a-bResult:
You can also download the PBIX file to have a view.
Regards,
Daniel He
