Forum Discussion

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi basyaiban ,

    You can add a new table with all year and growth and create a matrix with original table id on row, brand and new table column on columns, then write a measure to use current row content to lookup corresponding values.

    If you confused about coding formula, please share some sample data as table format for test.

    How to Get Your Question Answered Quickly  
    Regards,

    Xiaoxin Sheng

    • basyaiban's avatar
      basyaiban
      Frequent Visitor
      6 years ago

      Anonymous  Sorry. I gave you the wrong data yesterday. Here's my data.

       

      IDQuarterYearSales
      a1201651409.1
      a12017568727
      a22016556365
      a2201779091.8
      b12016127820
      b12017836365
      b22016261000
      b22017172727

       

      I wanna make this

      IDQuarter 1  Quarter 2  
       20162017 Growth 20162017Growth
      a      
      b      

       

      But I got this

      IDQuarter 1   Quarter 2   
       2016Growth2017Growth2016Growth2017Growth
      a        
      b        

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        HI basyaiban ,

        You can refer to the following steps to achieve your requirement:

        Steps:

        1. Create a new table to extract year from the original table and add 'growth'.

        T2 =
UNION ( VALUES ( T1[Year] ), { "Growth" } )

        2. Create a matrix visual with T1[id] as row, T1[quarter], T2[Year] as column. (Notice: current it does not work because these fields can;t mapping correctly)

        3. Write a measure to use correspond row/column labels as conditions to calculate and use on value fields of matrix.

        Measure 2 = 
VAR currYear =
    SELECTEDVALUE ( T2[Year] )
RETURN
    IF (
        ISFILTERED ( T2[Year] )
            && currYear <> "Growth",
        CALCULATE (
            SUM ( T1[Sales] ),
            FILTER ( ALLSELECTED ( T1 ), [Year] = VALUE ( currYear ) ),
            VALUES ( T1[Quarter] ),
            VALUES ( T1[ID] )
        ),
        VAR list =
            SELECTCOLUMNS (
                EXCEPT ( ALL ( T2[Year] ), VALUES ( T2[Year] ) ),
                "Y", VALUE ( [Year] )
            )
        VAR _min =
            CALCULATE (
                SUM ( T1[Sales] ),
                FILTER ( ALLSELECTED ( T1 ), [Year] = MINX ( list, [Y] ) ),
                VALUES ( T1[Quarter] ),
                VALUES ( T1[ID] )
            )
        VAR _max =
            CALCULATE (
                SUM ( T1[Sales] ),
                FILTER ( ALLSELECTED ( T1 ), [Year] = MAXX ( list, [Y] ) ),
                VALUES ( T1[Quarter] ),
                VALUES ( T1[ID] )
            )
        RETURN
            DIVIDE ( _max - _min, _min )
    )


        Regards,

        Xiaoxin Sheng