Forum Discussion
Calculating a variance
I'm trying calculate a variance and need help please. I'm trying to take submissions from Year 2018 subtract submissions from 2017, then divide that number by submissions from 2017. My problem is w...
I was using a table name where I should've used the variable....
Issue Variance =
VAR Issued = SUM('Activity Counts6'[Issued])
VAR Issue2017 = CALCULATE(Issued, 'Activity Counts6'[Year] = 2017)
VAR Issue2018 = CALCULATE(Issued, 'Activity Counts6'[Year] = 2018)
RETURN DIVIDE (Issue2018 - Issue2017, Issue2017)
Please provide a sample importable data, not an image. I would like to test the formula.
How can I provide that?
You may paste a data table here or post a link to your sample data.
This is my query
Issue Variance = VAR Issued = SUM('SubmissionActivity'[Issued]) VAR Issue2017 = CALCULATE(Issued, 'SubmissionActivity'[Year] = 2017) VAR Issue2018 = CALCULATE(Issued, 'SubmissionActivity'[Year] = 2018) RETURN DIVIDE (Issue2018 - Issue2017, Issue2017)Here is sample data with current and expected results
DAX Result What it should be Month Product Year Issued Issue Variance Issue Variance 1-Jan Remodeler Excl. Exist Structure - Residential 2018 27 0 0.50 1-Jan Remodeler Excl. Exist Structure - Residential 2017 18 0 0.80 1-Jan Remodeler Excl. Exist Structure - Residential 2016 10 0 -0.29 1-Jan Remodeler Excl. Exist Structure - Residential 2015 14 0 I appreciate your time