cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

Highlighted
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 Helper I
Helper I

Re: Compare Plan and actual data

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

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors