The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
102 | |
82 | |
62 | |
56 |
User | Count |
---|---|
254 | |
119 | |
115 | |
100 | |
76 |