Forum Discussion
Connecting values from measures
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?
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.
3 Replies
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.
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
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.