Forum Discussion

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

    • basyaiban's avatar
      basyaiban
      Frequent Visitor
      6 years ago

      Anonymous thank you. it works.

      I forgot that I must calculate the YTD too.

      I make measure "YTD = TOTALYTD(SUM(T1[Sales),T1[Date])"

      But I can't apply this because YTD is measure (sales is column), it can't work because there's SUM in the formula.

      Can I make the growth of YTD (in case : YTD is a measure not column).

       

      Thanks in advance.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi basyaiban ,

        If you mean you want to rolling calculate between 'quarter' fields of the same year, you can try to use following measure formula. I remove old quarter filter and add a rolling quarter filter instead.

        Measure 2 =
VAR currYear =
    SELECTEDVALUE ( T2[Year] )
RETURN
    IF (
        ISFILTERED ( T2[Year] )
            && currYear <> "Growth",
        CALCULATE (
            SUM ( T1[Sales] ),
            FILTER (
                ALLSELECTED ( T1 ),
                [Year] = VALUE ( currYear )
                    && [Quarter] <= MAX ( 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] )
                        && [Quarter] <= MAX ( T1[Quarter] )
                ),
                VALUES ( T1[ID] )
            )
        VAR _max =
            CALCULATE (
                SUM ( T1[Sales] ),
                FILTER (
                    ALLSELECTED ( T1 ),
                    [Year] = MAXX ( list, [Y] )
                        && [Quarter] <= MAX ( T1[Quarter] )
                ),
                VALUES ( T1[ID] )
            )
        RETURN
            DIVIDE ( _max - _min, _min )
    )

        Regards,

        Xioaxin Sheng