Connecting values from measures

[Anonymous]

Hello,

I have a measure in one table and I am trying to connect the values with a measure from another table. this is a continuation from my previous question https://community.powerbi.com/t5/Desktop/Sum-of-latest-values-of-all-groups/m-p/906426#M434408

The first measure is this (Gross Sales LE): 

Gross Sales LE HELP = 
VAR __myLastAmount = CALCULATE(LASTNONBLANK(Sales[Gross Sales LE (EUR)], ""), FILTER('Sales', Sales[EntryDate] = MAX(Sales[EntryDate])))
VAR __tmpTable = SUMMARIZE('Sales',[CustID], [Year], [ProductType], [Season],"__LastAmount",__myLastAmount)
RETURN SUMX(__tmpTable,[__LastAmount])

Gross Sales LE = 
VAR __myLastAmount = CALCULATE(LASTNONBLANK(Sales[Gross Sales LE (EUR)], ""), FILTER('Sales', Sales[EntryDate] = MAX(Sales[EntryDate])))
VAR __tmpTable = SUMMARIZE('Sales',[CustID], [Year], [ProductType], [Season],"__LastAmount",__myLastAmount)
RETURN SUMX(__tmpTable,[Gross Sales LE HELP])

The measure from the second table is similar, just called Net Sales. I would like to connect these measures based on fields CustID, Year, Season, ProductType which are in both tables. Is it somehow possible?

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

Here we go:

Table = 
VAR k =
    SUMMARIZE (
        'Sales (table1)',
        'Sales (table1)'[CustID],
        'Sales (table1)'[Year],
        'Sales (table1)'[Season],
        'Sales (table1)'[ProductType],
        "date", MAX ( 'Sales (table1)'[EntryDate] )
    )
VAR fil =
    ADDCOLUMNS (
        k,
        "Gross Sales LE", CALCULATE (
            SUM ( 'Sales (table1)'[Gross Sales LE (EUR)] ),
            FILTER ( 'Sales (table1)', 'Sales (table1)'[EntryDate] = [date] )
        ),
        "NetSalesPerc_", 1
            -
            VAR kk =
                ADDCOLUMNS (
                    'SalesPerc (table2)',
                    "maxd", CALCULATE (
                        MAX ( 'SalesPerc (table2)'[EntryDate] ),
                        ALLEXCEPT (
                            'SalesPerc (table2)',
                            'SalesPerc (table2)'[CustID],
                            'SalesPerc (table2)'[Year],
                            'SalesPerc (table2)'[Season],
                            'SalesPerc (table2)'[ProductType]
                        )
                    )
                )
            VAR fi =
                FILTER ( kk, 'SalesPerc (table2)'[EntryDate] = [maxd] )
            RETURN
                CALCULATE (
                    SUM ( 'SalesPerc (table2)'[NetSalesPerc] ),
                    FILTER (
                        'SalesPerc (table2)',
                        'SalesPerc (table2)'[CustID] = 'Sales (table1)'[CustID]
                            && 'SalesPerc (table2)'[Year] = 'Sales (table1)'[Year]
                            && 'SalesPerc (table2)'[Season] = 'Sales (table1)'[Season]
                            && 'SalesPerc (table2)'[ProductType] = 'Sales (table1)'[ProductType]
                    ),
                    KEEPFILTERS ( fi )
                ),
        "ProfitPerc_",
        VAR newk =
            ADDCOLUMNS (
                'SalesPerc (table2)',
                "maxdd", CALCULATE (
                    MAX ( 'SalesPerc (table2)'[EntryDate] ),
                    ALLEXCEPT (
                        'SalesPerc (table2)',
                        'SalesPerc (table2)'[CustID],
                        'SalesPerc (table2)'[Year],
                        'SalesPerc (table2)'[Season],
                        'SalesPerc (table2)'[ProductType]
                    )
                )
            )
        VAR fi =
            FILTER ( newk, 'SalesPerc (table2)'[EntryDate] = [maxdd] )
        RETURN
            CALCULATE (
                SUM ( 'SalesPerc (table2)'[ProfitPerc] ),
                FILTER (
                    'SalesPerc (table2)',
                    'SalesPerc (table2)'[CustID] = 'Sales (table1)'[CustID]
                        && 'SalesPerc (table2)'[Year] = 'Sales (table1)'[Year]
                        && 'SalesPerc (table2)'[Season] = 'Sales (table1)'[Season]
                        && 'SalesPerc (table2)'[ProductType] = 'Sales (table1)'[ProductType]
                ),
                KEEPFILTERS ( fi )
            )
    )
VAR c =
    ADDCOLUMNS ( fil, "gro", [NetSalesPerc_] * [Gross Sales LE] )
VAR d =
    ADDCOLUMNS ( c, "a", [ProfitPerc_] * [gro] )
RETURN
    SELECTCOLUMNS (
        d,
        "id", 'Sales (table1)'[CustID],
        "year", 'Sales (table1)'[Year],
        "Seadon", 'Sales (table1)'[Season],
        "Porduct type", 'Sales (table1)'[ProductType],
        " Net Sales", [gro],
        "Gross Margin", [a],
        "Gross Sales LE", [Gross Sales LE]
    )

For more details, please check the pbix as attached.

[v-frfei-msft]

Hi @Anonymous ,

Could you please share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[Anonymous]

Hello @v-frfei-msft ,

Here is a onedrive link to some random data I created:

https://1drv.ms/x/s!Ag_adS5VBxzvhps3bhu0eACgyRL1Tg

The Gross Sales LE measure I already created using the formulas in the original post

Best regards,

Ales