Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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] ) )
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,
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) )
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] )
Hi,
You may download my PBI file from here.
Hope this helps.
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] ) )
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,
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) )
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 )
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] )
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
Super!! All Examples are very valuable for me. Have a nice weekend.
Hi,
In the Query Editor, you simply need to delete the last "Remove columns" step.
Super!! Thank you. 🙂
HI @AIB,
Thanks a lot! it works excactly what I would like to see.
Best Regards,
SH Lee
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |