Variance Calculation

[Anonymous]

 Hi, 

 I got some requirement as mentioned below. Slicer P1 and P2 contains data in the form of (Q1 2020,Q2 2020,Q4 2019). First visuals shows the average of "Index Score" column. For displaying the data I am using Matrix visual. P1 Slicer(Q1 2020 is selected) controls the first visual , P2 Slicer(Q2 2020 is selected ) controls the second visual. In the third visual I have to show the difference of values between the two matrix visuals. I have shared the sample dataset below the first image. Kindly help me out with this if you have any idea on this kind of requirement.

This is how my data looks like.

MU	Area	Parameter Name	Period	Index Score
USA	Core Index	AETT	Q1 2020	6
USA	Core Index	AETT	Q2 2020	8
USA	Core Index	AETT	Q3 2020	9
USA	Core Index	AETT	Q4 2019	7
USA	People Index	DCSICR	Q1 2020	10
USA	People Index	DCSICR	Q2 2020	6
USA	People Index	DCSICR	Q3 2020	8
India	People Index	DCSICR	Q4 2019	9
India	People Index	DCSICR	Q1 2020	7
India	People Index	DCSICR	Q2 2020	10
India	People Index	DCSICR	Q3 2020	6
Canada	Transformation Index	Ankur	Q4 2019	8
Canada	Transformation Index	Ankur	Q1 2020	9
USA	Transformation Index	Ankur	Q2 2020	7
USA	Transformation Index	Ankur	Q3 2020	10
USA	Transformation Index	Ankur	Q4 2019	6
USA	Transformation Index	RAT	Q1 2020	8
USA	Transformation Index	RAT	Q2 2020	9
USA	Transformation Index	TPLANE	Q3 2020	7
USA	Transformation Index	TPLANE	Q4 2019	10
Canada	Tools & Method Standardization Index	CSIP	Q1 2020	6
Canada	Tools & Method Standardization Index	CQSC	Q2 2020	8
Canada	Tools & Method Standardization Index	CSIP	Q3 2020	9
USA	Tools & Method Standardization Index	Ankur	Q4 2019	7
USA	Tools & Method Standardization Index	Ankur	Q1 2020	10
USA	Tools & Method Standardization Index	CQSC	Q2 2020	6
USA	Tools & Method Standardization Index	CSIP	Q3 2020	8

[Anonymous · Accepted solution]

Hi @Anonymous ,

The pbix file is here.

1.Create two separate period tables

2.Create three measures, 

P1Measure = CALCULATE(SUM('Table'[Index Score]),FILTER('Table',[Period]=SELECTEDVALUE(P1[Period])))

P2Measure = CALCULATE(SUM('Table'[Index Score]),FILTER('Table',[Period]=SELECTEDVALUE(P2[Period])))

Variance = [P2Measure]-[P1Measure]

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , for this you need two independent period table.

Refer to my blog on the same topic with a date range

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601