Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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.
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.
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
User | Count |
---|---|
98 | |
90 | |
78 | |
72 | |
65 |
User | Count |
---|---|
114 | |
97 | |
95 | |
68 | |
65 |