Add Variance Column to Matrix

[bernate]

Hello, I am looking to add a variance column to my matrix. The variance % calculation would be 1-(column2/column1).

Here is what my report currently looks like. I have a slicer that selects the columns in the matrix and I would need the variance to change based on the months selected.

Here is the result I want:

I have 14 attributes and they would change every year, so I can't hardcode the attribute names into my query. 

[Anonymous · Accepted solution]

Hi @bernate 

Please try this:

First of all, I create a set of sample data:

Then create a new table with dax:

Table 2 = {1,2,3}

Then add a measure:

MEASURE =
VAR _newvalue =
    SELECTEDVALUE ( 'Table 2'[Value] )
VAR _columns =
    SWITCH (
        _newvalue,
        1, MAX ( 'Table'[Column1] ),
        2, MAX ( 'Table'[Column2] ),
        3, MAX ( 'Table'[Column3] )
    )
RETURN
    _columns

Then add a matrix and a slicer:

Then add a measure:

% =
VAR _maxvalue =
    MAXX ( ALLSELECTED ( 'Table 2' ), 'Table 2'[Value] )
VAR _minvalue =
    MINX ( ALLSELECTED ( 'Table 2' ), 'Table 2'[Value] )
RETURN
    IF (
        MAX ( 'Table 2'[Value] ) = _maxvalue
            && _maxvalue <> _minvalue,
        1
            - CALCULATE ( 'Table'[Measure], 'Table 2'[Value] = _maxvalue )
                / CALCULATE ( 'Table'[Measure], 'Table 2'[Value] = _minvalue )
    )

The result is as follow:

Best Regards

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

[amitchandak]

@bernate , You switch the grand total with varinace

Measure =
var _min = minx(allselected(Date), Date[Date])
var _max = minx(allselected(Date), Date[Date])
return
if(isinscope(Date[Column month Year]) , [Measure], calculate([Meausre], filetr(Date, eomonth(Date[Date],0) = eomonth(_max,0))) -
calculate([Meausre], filetr(Date, eomonth(Date[Date],0) = eomonth(_min,0))) )

How to Switch Subtotal and Grand Total in Power BI | Power BI Tutorials| isinscope: https://youtu.be/smhIPw3OkKA