add growth column in table

[basyaiban]

I have dataset in table 1, then I make table 2 in powerbi. How can I make growth colu

[v-shex-msft]

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]

@v-shex-msft  Sorry. I gave you the wrong data yesterday. Here's my data.

ID	Quarter	Year	Sales
a	1	2016	51409.1
a	1	2017	568727
a	2	2016	556365
a	2	2017	79091.8
b	1	2016	127820
b	1	2017	836365
b	2	2016	261000
b	2	2017	172727

I wanna make this

ID	Quarter 1			Quarter 2
	2016	2017	Growth	2016	2017	Growth
a
b

But I got this

ID	Quarter 1				Quarter 2
	2016	Growth	2017	Growth	2016	Growth	2017	Growth
a
b

[v-shex-msft]

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]

@v-shex-msft 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.

[v-shex-msft]

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

[basyaiban]

I edited your formula SUM(T1[Sales]) with SUMX(T1,[YTD]). I use SUMX because YTD is a measure, not column.

It can run when I use small data. But when I use bigger data, there's warning like "power bi couldn't load the data for this visual not enough memory"

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

@v-shex-msft

[v-shex-msft]

HI @basyaiban ,

Can you please share a sample pbix file for test? It is hard to test without any detail data.

Notice: do mask on sensitive data before share.

Regards,
Xiaoxin Sheng

[basyaiban]

@v-shex-msft 

there is  SUM ( T1[Sales] ) in your formula. It can running because Sales is a column.

But, I can't running SUM ( [YTD] ) because its a measure. I add new measure called YTD (Year to Date). YTD is calculated from YTD = TOTALYTD(SUM(T1[Sales]),T1[Date])

I want to make table like this. But the value is not sales again. Its value is from YTD

ID	Quarter 1			Quarter 2
	2016	2017	Growth	2016	2017	Growth
a
b