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

Compare Plan and actual data

Hello All,

 

I want to see the differences between Plan and Actual. I got the 2 tables form excel below. How do I make a table what I would like to see below?

 

20190117_173653.png

 

 

Thanks in advance.

 

4 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Compare Plan and actual data

Hi @bluetronics

 

Although I believe it would be more convenient to do this in the query editor (M), you can also try creating a new calculated table with DAX:

 

TestTable =
VAR _BaseTable =
    DISTINCT (
        UNION (
            SUMMARIZECOLUMNS ( Actual[Customer]; Actual[Product] );
            SUMMARIZECOLUMNS ( Plan[Customer]; Plan[Product] )
        )
    )
RETURN
    ADDCOLUMNS (
        _BaseTable;
        "Difference"; LOOKUPVALUE (
            Actual[Qty];
            Actual[Customer]; [Customer];
            Actual[Product]; [Product]
        )
            - LOOKUPVALUE ( Plan[Qty]; Plan[Customer]; [Customer]; Plan[Product]; [Product] )
    )

 

Code formatted with   www.daxformatter.com

bluetronics Regular Visitor
Regular Visitor

Re: Compare Plan and actual data

HI @ AIB,

Your instruction was great to work in general but I got an issue with "A table of multiple values was supplied where a single value was expected."  when I apply to big data. And I found it why but I don't know how to clear the issue. Can you help me agin?

I can see the error message when there are differnt lookupvalue. Do you happen to know how to do if I want to sum of Qty for the same customer and product.

 

20190119_014109.png

Regards,

 

Highlighted
Super User
Super User

Re: Compare Plan and actual data

@bluetronics

 

NewTable = 
VAR _BaseTable =
    DISTINCT (
        UNION (
            SUMMARIZECOLUMNS ( Actual[Customer]; Actual[Product] );
            SUMMARIZECOLUMNS ( Plan[Customer]; Plan[Product] )
        )
    )
RETURN
    ADDCOLUMNS (
        _BaseTable;
        "Difference"; 
        VAR _CurrentCustomer=[Customer]
        VAR _CurrentProduct=[Product] 
        RETURN 
           CALCULATE(SUM(Actual[Qty]); Actual[Customer]=_CurrentCustomer; Actual[Product]=_CurrentProduct)
           - CALCULATE(SUM(Plan[Qty]); Plan[Customer]=_CurrentCustomer; Plan[Product]=_CurrentProduct)
    )
Super User
Super User

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

13 REPLIES 13
Super User
Super User

Re: Compare Plan and actual data

Hi @bluetronics

 

Although I believe it would be more convenient to do this in the query editor (M), you can also try creating a new calculated table with DAX:

 

TestTable =
VAR _BaseTable =
    DISTINCT (
        UNION (
            SUMMARIZECOLUMNS ( Actual[Customer]; Actual[Product] );
            SUMMARIZECOLUMNS ( Plan[Customer]; Plan[Product] )
        )
    )
RETURN
    ADDCOLUMNS (
        _BaseTable;
        "Difference"; LOOKUPVALUE (
            Actual[Qty];
            Actual[Customer]; [Customer];
            Actual[Product]; [Product]
        )
            - LOOKUPVALUE ( Plan[Qty]; Plan[Customer]; [Customer]; Plan[Product]; [Product] )
    )

 

Code formatted with   www.daxformatter.com

bluetronics Regular Visitor
Regular Visitor

Re: Compare Plan and actual data

HI @AIB,

Thanks a lot! it works excactly what I would like to see.

Best Regards,

SH Lee

bluetronics Regular Visitor
Regular Visitor

Re: Compare Plan and actual data

HI @ AIB,

Your instruction was great to work in general but I got an issue with "A table of multiple values was supplied where a single value was expected."  when I apply to big data. And I found it why but I don't know how to clear the issue. Can you help me agin?

I can see the error message when there are differnt lookupvalue. Do you happen to know how to do if I want to sum of Qty for the same customer and product.

 

20190119_014109.png

Regards,

 

Highlighted
Super User
Super User

Re: Compare Plan and actual data

@bluetronics

 

NewTable = 
VAR _BaseTable =
    DISTINCT (
        UNION (
            SUMMARIZECOLUMNS ( Actual[Customer]; Actual[Product] );
            SUMMARIZECOLUMNS ( Plan[Customer]; Plan[Product] )
        )
    )
RETURN
    ADDCOLUMNS (
        _BaseTable;
        "Difference"; 
        VAR _CurrentCustomer=[Customer]
        VAR _CurrentProduct=[Product] 
        RETURN 
           CALCULATE(SUM(Actual[Qty]); Actual[Customer]=_CurrentCustomer; Actual[Product]=_CurrentProduct)
           - CALCULATE(SUM(Plan[Qty]); Plan[Customer]=_CurrentCustomer; Plan[Product]=_CurrentProduct)
    )
bluetronics Regular Visitor
Regular Visitor

Re: Compare Plan and actual data

Super!! Thank you. :-)

 

Super User
Super User

Re: Compare Plan and actual data

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png

bluetronics Regular Visitor
Regular Visitor

Re: Compare Plan and actual data

Hi @AlB and @Ashish_Mathur

Can you tell me how to show data additionally? 

 

20190119_155231.png 

Best Regards,

 

Super User
Super User

Re: Compare Plan and actual data

Hi,

 

In the Query Editor, you simply need to delete the last "Remove columns" step.

bluetronics Regular Visitor
Regular Visitor

Re: Compare Plan and actual data

Hi @Ashish_Mathur,

I would like to know how to work with DAX.  Thanks.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 376 members 3,746 guests
Please welcome our newest community members: