Forum Discussion

Fcoatis's avatar
Fcoatis
Post Patron
7 years ago
Solved

ADDCOLUMNS

I need help with this one:

Can  I calculate a third column from the two just created?

Category Budget = 
    ADDCOLUMNS(
        CROSSJOIN(SUMMARIZECOLUMNS('Product Category'[Category]);DISTINCT('Date'[Month]));
        "2009 Sales"; CALCULATE([Sales Amount];'Date'[Calendar Year Number]=2009);
        "Factor" ; LOOKUPVALUE('Product Category'[Budget];'Product Category'[Category];[Category])/*;
        "Budget 2010"; [2009 Sales] * [Factor]*/
    )

Need Help
  • AlB's avatar
    AlB
    7 years ago

    Hi Fcoatis 

    Yes, you just need an additional ADDCOLUMNS:

    Category Budget =
ADDCOLUMNS (
    ADDCOLUMNS (
        CROSSJOIN (
            SUMMARIZECOLUMNS ( 'Product Category'[Category] );
            DISTINCT ( 'Date'[Month] )
        );
        "2009 Sales"; CALCULATE ( [Sales Amount]; 'Date'[Calendar Year Number] = 2009 );
        "Factor"; LOOKUPVALUE (
            'Product Category'[Budget];
            'Product Category'[Category]; [Category]
        )
    );
    "Extra column"; [Factor] * [2009 Sales]
)

    I've set the product of the two columns as example

2 Replies

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    Hi Fcoatis 

    Yes, you just need an additional ADDCOLUMNS:

    Category Budget =
ADDCOLUMNS (
    ADDCOLUMNS (
        CROSSJOIN (
            SUMMARIZECOLUMNS ( 'Product Category'[Category] );
            DISTINCT ( 'Date'[Month] )
        );
        "2009 Sales"; CALCULATE ( [Sales Amount]; 'Date'[Calendar Year Number] = 2009 );
        "Factor"; LOOKUPVALUE (
            'Product Category'[Budget];
            'Product Category'[Category]; [Category]
        )
    );
    "Extra column"; [Factor] * [2009 Sales]
)

    I've set the product of the two columns as example