cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User III
Super User III

Re: Compare Plan and actual data

@bluetronics

You can do it by following the same logic, adding more columns through ADDCOLUMNS:

 

NewTableExtended = 
VAR _BaseTable =
    DISTINCT (
        UNION (
            SUMMARIZECOLUMNS ( Actual[Customer]; Actual[Product] );
            SUMMARIZECOLUMNS ( Plan[Customer]; Plan[Product] )
        )
    )
RETURN
    ADDCOLUMNS (
        _BaseTable;
        "Plan-Qty";
        VAR _CurrentCustomer = [Customer]
        VAR _CurrentProduct = [Product]
        VAR _CurrentPlan =
            CALCULATE (
                SUM ( Plan[Qty] );
                Plan[Customer] = _CurrentCustomer;
                Plan[Product] = _CurrentProduct
            )
        RETURN
            _CurrentPlan;
        "Actual-Qty";
        VAR _CurrentCustomer = [Customer]
        VAR _CurrentProduct = [Product]
        VAR _CurrentActual =
            CALCULATE (
                SUM ( Actual[Qty] );
                Actual[Customer] = _CurrentCustomer;
                Actual[Product] = _CurrentProduct
            )
        RETURN
            _CurrentActual;
        "Diff. Qty";
        VAR _CurrentCustomer = [Customer]
        VAR _CurrentProduct = [Product]
        VAR _CurrentActual =
            CALCULATE (
                SUM ( Actual[Qty] );
                Actual[Customer] = _CurrentCustomer;
                Actual[Product] = _CurrentProduct
            )
        VAR _CurrentPlan =
            CALCULATE (
                SUM ( Plan[Qty] );
                Plan[Customer] = _CurrentCustomer;
                Plan[Product] = _CurrentProduct
            )
        RETURN
            _CurrentActual - _CurrentPlan
    )

Code formatted with   www.daxformatter.com

Super User III
Super User III

Re: Compare Plan and actual data

@bluetronics

Or actually we could have another version with two nested ADDCOLUMNS to make the code a bit less verbose and avoid some recalculations which I'm guessing would be faster although I cannot be positive about it

 

NewTableExtended_v2 = 
VAR _BaseTable =
    DISTINCT (
        UNION (
            SUMMARIZECOLUMNS ( Actual[Customer]; Actual[Product] );
            SUMMARIZECOLUMNS ( Plan[Customer]; Plan[Product] )
        )
    )
RETURN
    ADDCOLUMNS (
        ADDCOLUMNS (
            _BaseTable;
            "Plan-Qty";
            VAR _CurrentCustomer = [Customer]
            VAR _CurrentProduct = [Product]
            VAR _CurrentPlan =
                CALCULATE (
                    SUM ( Plan[Qty] );
                    Plan[Customer] = _CurrentCustomer;
                    Plan[Product] = _CurrentProduct
                )
            RETURN
                _CurrentPlan;
            "Actual-Qty";
            VAR _CurrentCustomer = [Customer]
            VAR _CurrentProduct = [Product]
            VAR _CurrentActual =
                CALCULATE (
                    SUM ( Actual[Qty] );
                    Actual[Customer] = _CurrentCustomer;
                    Actual[Product] = _CurrentProduct
                )
            RETURN
                _CurrentActual
        );
        "Diff. Qty"; [Actual-Qty] - [Plan-Qty]
    )

Code formatted with   www.daxformatter.com

View solution in original post

Super User III
Super User III

Re: Compare Plan and actual data

@bluetronics

 

and since I seem to be in the mood to write today, here's yet another version without variables. It would appear leaner (also because I am keeping CALCULATEs in the same line) but it might be a bit less easy to follow and thus to maintain. Matter of taste I guess. 

 

NewTableExtended_v3= 
VAR _BaseTable =
    DISTINCT (
        UNION (
            SUMMARIZECOLUMNS ( Actual[Customer]; Actual[Product] );
            SUMMARIZECOLUMNS ( Plan[Customer]; Plan[Product] )
        )
    )
RETURN
    ADDCOLUMNS (
        ADDCOLUMNS (
            _BaseTable;
            "Plan-Qty"; CALCULATE (SUM ( Plan[Qty] ); Plan[Customer] = EARLIER ( [Customer] );Plan[Product] = EARLIER ( [Product] ));
            "Actual-Qty"; CALCULATE (SUM ( Actual[Qty] ); Actual[Customer] = EARLIER ( [Customer] ); Actual[Product] = EARLIER ( [Product] )
            )
        );
        "Diff. Qty"; [Actual-Qty] - [Plan-Qty]
    )

 

Ok. Enough versions Smiley Happy

bluetronics Regular Visitor
Regular Visitor

Re: Compare Plan and actual data

Super!! All Examples are very valuable for me. Have a nice weekend. Smiley Happy

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors