Forum Discussion

basyaiban

Frequent Visitor

6 years ago

add growth column in table

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

Frequent Visitor

6 years ago

Anonymous 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

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

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

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

basyaiban
Frequent Visitor
6 years ago
Anonymous
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