cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

add growth column in table

WhatsApp Image 2019-11-20 at 16.32.13.jpeg

 

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

8 REPLIES 8
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

 

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)

8.png

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 )
    )

9.png
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@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.

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 )
    )

15.png

Regards,

Xioaxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@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

IDQuarter 1  Quarter 2  
 20162017 Growth 20162017Growth
a      
b      

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors