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. 

[PBIdashboards]

The accepted DAX solution handles the dynamic slicer case well. One practical issue as your report scales: with 14 attributes changing every year, you'll need to revisit and update the SWITCH logic annually it's maintainable but not zero-effort.

For the dynamic variance + slicer combination specifically, Flexa Tables on AppSource handles this without the measure maintenance users pick which two periods to compare directly in the published report, variance recalculates automatically. Search "Flexa Tables" on AppSource, free trial available.

[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