Summarizecolumns from two tables

[jamuka]

Hello,

I'd like to make a one table from my sales and budget tables, which should includes Turnover and Margin amounts based on location, category and subcategory columns. Both tables have relationship with my Date table.

I tried to use SUMMARIZECOLUMNS but I couldn't add amounts from two tables, therefore I used

LOOKUPVALUE. I read some posts in here also in other sites about SUMMARIZECOLUMNS but couldn't grasp the whole logic of it.

Also at the moment my final table shows date in future, because I made my Date table via DAX so it shows whole year, could it be possible to show untill maximum sales date? below you can see my final table, I added  sales columns via LOOKUPVALUE.

 

kind regards

 

My Budget Table

Date	Category	Sub Category	Location	Budget Turnover	Budget Margin
15.08.2020	C1	S1	A	€ 75.000	€ 8.000
15.08.2020	C1	S2	A	€ 75.000	€ 8.000
15.08.2020	C1	S3	A	€ 75.000	€ 8.000
15.08.2020	C2	S4	A	€ 60.000	€ 10.000
15.08.2020	C2	S5	A	€ 60.000	€ 10.000
15.08.2020	C1	S1	B	€ 93.750	€ 10.000
15.08.2020	C1	S2	B	€ 93.750	€ 10.000
15.08.2020	C1	S3	B	€ 93.750	€ 10.000
15.08.2020	C2	S4	B	€ 75.000	€ 12.500
15.08.2020	C2	S5	B	€ 75.000	€ 12.500
16.08.2020	C1	S1	A	€ 75.000	€ 8.000
16.08.2020	C1	S2	A	€ 75.000	€ 8.000
16.08.2020	C1	S3	A	€ 75.000	€ 8.000
16.08.2020	C2	S4	A	€ 60.000	€ 10.000
16.08.2020	C2	S5	A	€ 60.000	€ 10.000
16.08.2020	C1	S1	B	€ 93.750	€ 10.000
16.08.2020	C1	S2	B	€ 93.750	€ 10.000
16.08.2020	C1	S3	B	€ 93.750	€ 10.000
16.08.2020	C2	S4	B	€ 75.000	€ 12.500
16.08.2020	C2	S5	B	€ 75.000	€ 12.500
17.08.2020	C1	S1	A	€ 75.000	€ 8.000
17.08.2020	C1	S2	A	€ 75.000	€ 8.000
17.08.2020	C1	S3	A	€ 75.000	€ 8.000

My Sales Table

Date	Category	Sub Category	Location	Sales Turnover	Sales Margin
15.08.2020	C1	S1	A	€ 65.000	€ 7.250
15.08.2020	C1	S2	A	€ 65.000	€ 7.250
15.08.2020	C1	S3	A	€ 65.000	€ 7.250
15.08.2020	C2	S4	A	€ 50.000	€ 9.000
15.08.2020	C2	S5	A	€ 50.000	€ 9.000
15.08.2020	C1	S1	B	€ 87.000	€ 9.000
15.08.2020	C1	S2	B	€ 87.000	€ 9.000
15.08.2020	C1	S3	B	€ 87.000	€ 9.000
15.08.2020	C2	S4	B	€ 66.000	€ 11.000
15.08.2020	C2	S5	B	€ 66.000	€ 11.500
16.08.2020	C1	S1	A	€ 65.000	€ 7.000
16.08.2020	C1	S2	A	€ 65.000	€ 7.000
16.08.2020	C1	S3	A	€ 65.000	€ 7.000
16.08.2020	C2	S4	A	€ 55.000	€ 9.000
16.08.2020	C2	S5	A	€ 55.000	€ 9.100
16.08.2020	C1	S1	B	€ 95.000	€ 9.000
16.08.2020	C1	S2	B	€ 85.000	€ 9.000
16.08.2020	C1	S3	B	€ 85.000	€ 9.000
16.08.2020	C2	S4	B	€ 72.000	€ 12.000
16.08.2020	C2	S5	B	€ 69.000	€ 12.000

I create my Date Table with CALENDARAUTO based on my Sales Table

Budget vs Sales = 
SUMMARIZECOLUMNS(
'Date'[Date],
        'Budget'[Category],
        'Budget'[Location],
        'Budget'[Subcategory],
"Budget Turnover",SUM('Budget'[Budget Turnover]),
"Budget Margin",SUM('Budget'[Budget Margin])

Final TableModel

[v-zhenbw-msft]

Hi @jamuka ,

Maybe you can try this formula to create a table.

Budget vs Sales =
SUMMARIZECOLUMNS (
    'Date'[Date],
    'Budget'[Category],
    'Budget'[Location],
    'Budget'[Subcategory],
    "Budget Turnover", CALCULATE ( SUM ( 'Budget'[Budget Turnover] ) ),
    "Budget Margin", CALCULATE ( SUM ( 'Budget'[Budget Margin] ) )
)

Or you can create two columns in Sales table. One is Budget Turnover, another is Budget Margin.

Budget Turnover = 
CALCULATE(
    SUM('Budget Table'[Budget Turnover]),
    FILTER(
        'Budget Table',
        'Budget Table'[Date]='Sales Table'[Date]
        &&'Budget Table'[Category]='Sales Table'[Category]
        &&'Budget Table'[Sub Category]='Sales Table'[Sub Category]
        &&'Budget Table'[Location]='Sales Table'[Location]
        ))

Budget Margin = 
CALCULATE(
    SUM('Budget Table'[Budget Margin]),
    FILTER(
        'Budget Table',
        'Budget Table'[Date]='Sales Table'[Date]
        &&'Budget Table'[Category]='Sales Table'[Category]
        &&'Budget Table'[Sub Category]='Sales Table'[Sub Category]
        &&'Budget Table'[Location]='Sales Table'[Location]
        ))

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[jamuka]

Hello @v-zhenbw-msft,

thanks for your detailed answer. I already use your solution in my current table. What I want to learn is logic of summarizecolumns and how to create one table from two tables with one code. instead of adding columns (after summarizecolumns) with lookupvalue formula.

regards

[v-zhenbw-msft · Accepted solution]

Hi @jamuka ,

Maybe you can refer this formula to create a calculate table.

Table = 
ADDCOLUMNS (
    SUMMARIZE (
        'Sales Table',
        'Sales Table'[Date],
        'Sales Table'[Category],
        'Sales Table'[Sub Category],
        'Sales Table'[Location]
    ),
    "Turnover1", CALCULATE ( SUM ( 'Sales Table'[Sales Turnover] ) ),
    "Margin1", CALCULATE ( SUM ( 'Sales Table'[Sales Margin] ) ),
    "Turnover2", CALCULATE (
        SUM ( 'Budget Table'[Budget Turnover] ),
        FILTER (
            'Budget Table',
            'Budget Table'[Date] = 'Sales Table'[Date]
                && 'Budget Table'[Category] = 'Sales Table'[Category]
                && 'Budget Table'[Sub Category] = 'Sales Table'[Sub Category]
                && 'Budget Table'[Location] = 'Sales Table'[Location]
        )
    ),
    "Margin2", CALCULATE (
        SUM ( 'Budget Table'[Budget Margin] ),
        FILTER (
            'Budget Table',
            'Budget Table'[Date] = 'Sales Table'[Date]
                && 'Budget Table'[Category] = 'Sales Table'[Category]
                && 'Budget Table'[Sub Category] = 'Sales Table'[Sub Category]
                && 'Budget Table'[Location] = 'Sales Table'[Location]
        )
    )
)

Or you can merge the two tables as new in Power Query Editor.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[jamuka]

thank you, it works.

have a nice week.

[Fowmy]

@jamuka 

Your formula doesn't show sales table columns, I think it is incomplete. can you show the complete formula and let me know the error or the issue you are facing?

How to paste sample data with your question?
How to get your questions answered quickly?

_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[jamuka]

Hello @Fowmy,

When I added Sales table columns to my formula it shows sum of all day, instead of combination of location, category and sub category. Thefefore I add sales table columns with lookupvalue formula. 

I want my code is faster, I 'm not facing an error actually also at the moment my table shows future days too. What I want is to show dates if it's in my Sales table. I included screenshots and table's data to my post.

regards

[Greg_Deckler]

@jamuka - Might try using SUMX(RELATEDTABLE...(),...) in some of your column calculations. Hard to say exactly, might try experimenting with GROUPBY and SUMMARIZE as well. If your Date column is related to your Budget table, just use the Date field from the Budget table instead of the Date field from the Date table.

Otherwise, Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.