Forum Discussion
Calculate Variance in the same Column
Hi All,
I have a complex situation where the data is in the below format
Version Value date
QGP 12324 04/08/2018
QGP 13242 04/09/2018
QGP 23222 04/10/2018
.
.
.
Actual 13322 04/08/2018
Actual 13443 04/09/2018
Actual 13433 04/10/2018
.
.
.
Now I have to calculate the Variance ( which is QGP - Actual) this has to come in as another row in the same column.
Help me with suggestions/formulae of how to achieve this using DAX
You could probably accomplish this but it would probably be a bit of a pain. Basically, use VAR to create temp tables, one with your table as is and another where you create your variance row. Then you UNION them together and this is the RETURN for a calculated New Table. Too late to get more in-depth but the tricky part will be creating that single row table from scratch, that's always a pain.
2 Replies
I'm pretty sure you cannot add rows using DAX. The closest that you can get to the solution that you want is to create a new table with the dates in one column and the difference in another column. Then you can create a relationship between the two table using the dates columns.
You could probably accomplish this but it would probably be a bit of a pain. Basically, use VAR to create temp tables, one with your table as is and another where you create your variance row. Then you UNION them together and this is the RETURN for a calculated New Table. Too late to get more in-depth but the tricky part will be creating that single row table from scratch, that's always a pain.
