adding variance into table

[topazz11]

is there a way to add variance between current and prior into the table?

current and prior data are in the same table but I am not sure how to add the variance data.

Thank you,

type	group	group2	color	name	value1	value2	value3	value4	value5
current	aaa	group name1	color name12	name3	5	5	5	5	6
current	ccc	group name2	color name13	name4	5	5	5	5	8
current	bbb	group name3	color name14	name5	5	6	7	8	6
current	ddd	group name4	color name15	name6	5	5	9	5	8
current	ccc	group name5	color name16	name7	9	8	5	5	6
current	bbb	group name6	color name17	name8	9	9	5	5	6
current	ddd	group name7	color name18	name9	5	5	10	5	6
current	ccc	group name8	color name19	name10	8	5	5	44	6
current	bbb	group name9	color name20	name11	7	5	5	55	33
current	ddd	group name10	color name21	name12	6	5	5	5	6
prior	aaa	group name1	color name12	name3	2	3	4	5	6
prior	ccc	group name2	color name13	name4	3	2	5	4	9
prior	bbb	group name3	color name14	name5	4	1	6	3	12
prior	ddd	group name4	color name15	name6	5	0	7	2	15
prior	ccc	group name5	color name16	name7	6	-1	8	1	18
prior	bbb	group name6	color name17	name8	7	-2	9	0	21
prior	ddd	group name7	color name18	name9	8	-3	10	-1	24
prior	ccc	group name8	color name19	name10	9	-4	11	-2	27
prior	bbb	group name9	color name20	name11	10	-5	12	-3	30
prior	ddd	group name10	color name21	name12	11	-6	13	-4	33
variance
variance
variance
variance
variance
variance
variance
variance
variance
variance

[danextian · Accepted solution]

Hi @topazz11 

Instead of adding extra rows to your data and potentially slow down refresh and increase your semantic model's footprint, use calculation groups instead so the same logic is applied to the measures added to a visual. Please see the attached sample pbix.

[topazz11]

Thank you, this works. I have never used this before.  how does it filter current, prior or variance without relationship? it is mystery..

[topazz11]

I am trying to add all in one page and have them change dynamically based on the slicer.

[ToddChitt]

Question: Why do you want to have the Variance on additional ROWS, not as calculated a Column? Better yet, a set of MEASURES for SUM (Current}, SUM (Prior). Then Variance is just another measure

= SUM(Current) - SUM(Prior)

Putting Variance on its own set of rows does not seem logical. Do you have a pressing need to do it that particular way? If so, I would be curious to know the reason.

[topazz11]

I'm trying to display tables in one page using a slicer, allowing users to select 'Current', 'Prior', or 'Variance' and display the corresponding page based on their selection

[rohit1991]

Hi @topazz11 

If your goal is to display Current, Prior, and Variance on the same page and switch between them dynamically using a slicer, then the cleanest and most scalable solution is to use Calculation Groups via Tabular Editor. This lets you define one set of logic for your measures, and simply toggle between views without duplicating data or adding extra rows.

It keeps your model lean, improves performance, and gives end users a smooth slicer-based experience.

That said, if you still need to physically calculate variance rows (for audit, export, or other use cases), you can absolutely do that by:

• Grouping by a common key (like name + group),

• Joining current and prior rows in Power Query or DAX, and

• Calculating the variance as Current - Prior.

[topazz11]

yes, please guide me.