Forum Discussion

rpinxt's avatar
rpinxt
Solution Sage
2 years ago

Calculate the variance between two (variable) selections

I have this simplified view: As you can see now LBE1 and LBE2 are selected. I want to ad a 3rd field (measure) which will give the variance between the selections. The slicer on the right is...
Need Help
rpinxt's avatar
rpinxt
Solution Sage
2 years ago

Well maybe it is just not possible with the current data setup.

How would you guys do something like this?
How would your data look?

Cannot be the first one that wants to look at his data in this way?? 🤔

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi, rpinxt 

You can implement the calculation variance in total. I've tried adding another measure to the matrix, but that would have it in every column of the matrix:

As you can see, while the variance of the selected two can be successfully calculated, this will put the measure of this variance into the columns of each matrix.

Instead, you can calculate the variance for two columns in the matrix and display it in the total.

Sum of Addendum =
IF (
    NOT HASONEVALUE ( 'Table'[Type] ),
    AVERAGEX (
        'Table',
        CALCULATE ( SUM ( 'Table'[Addendum] ), 'Table'[Type] = "LBE1" )
            - CALCULATE ( SUM ( 'Table'[Addendum] ), 'Table'[Type] = "LBE2" )
    ),
    SUM ( 'Table'[Addendum] )
)

You can then control your slicer options, check the two columns you want to show variance, and uncheck the other columns. I've provided the PBIX file used this time below.

 

 

 

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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