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 

Yes, you can add a variance row to your table by calculating the difference between the "current" and "prior" values for each corresponding row. Since both current and prior data are in the same table, you’ll need to group them by a common identifier—in your case, fields like name, group, or a combination that uniquely matches each pair. You can create a new table or Power BI measure that joins the current and prior rows by these identifiers, and then subtract the prior values from the current ones to compute variance for each of the value1 to value5 columns.

This could be done using Power Query (M) by pivoting and merging the current and prior rows, or using DAX if you're working with calculated tables or measures. Once the variance is calculated, you can append those rows back into your main table or display them in a visual alongside the original data. This approach will allow you to easily analyze differences over time or between data snapshots. Let me know if you’d like step-by-step guidance for Power BI or Excel.

[topazz11]

yes, please guide me.