Forum Discussion

bernate's avatar
bernate
Helper III
2 years ago
Solved

Add Variance Column to Matrix

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. 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
    amitchandak
    Super User
    2 years ago

    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

  • PBIdashboards's avatar
    PBIdashboards
    Post Patron
    2 months ago

    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.