Compare Plan and actual data

[bluetronics]

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?

Thanks in advance.

[AlB · Accepted solution]

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  

[bluetronics · Accepted solution]

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.

Regards,

[AlB · Accepted solution]

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

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[AlB · Accepted solution]

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  

[bluetronics · Accepted solution]

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.

Regards,

[AlB · Accepted solution]

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

[AlB]

@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  

[AlB · Accepted solution]

@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  

[AlB]

@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 

[bluetronics]

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

[bluetronics]

Hi @AlB and @Ashish_Mathur

Can you tell me how to show data additionally? 

 

Best Regards,

[Ashish_Mathur]

Hi,

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

[bluetronics]

Hi @Ashish_Mathur,

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

[bluetronics]

Super!! Thank you. 🙂

[bluetronics]

HI @AIB,

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

Best Regards,

SH Lee