Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi mates
I have 2 fact tables
Sales
Targets
Common field between 2 tables is only ProductGroup but is many to many
I need to display table visual to show
Month, Year, Product Group, Target, Total_Sales
How do I relate these 2 tables and create measure to show as above
Solved! Go to Solution.
Hi @rajanimaddala ,
Here are my sample tables.
Use “Left outer” to merge these two tables.
Add three new columns.
Year = YEAR ( Merge1[Date] )
Month = MONTH ( Merge1[Date] )
Column = IF ( Merge1[Year] * 100 + Merge1[Month] = Merge1[Targets.YM], TRUE (), FALSE () )
Create a new table to make visual.
FinalTable = CALCULATETABLE ( Merge1, Merge1[Column] = TRUE () )
Add a new measure “TotalSales”.
TotalSales = CALCULATE ( SUM ( 'FinalTable'[Sales] ), ALLEXCEPT ( 'FinalTable', 'FinalTable'[Targets.YM], 'FinalTable'[Product Group] ) )
According to my ideas to get the results you want.
Best Regards,
Eads
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @rajanimaddala ,
Here are my sample tables.
Use “Left outer” to merge these two tables.
Add three new columns.
Year = YEAR ( Merge1[Date] )
Month = MONTH ( Merge1[Date] )
Column = IF ( Merge1[Year] * 100 + Merge1[Month] = Merge1[Targets.YM], TRUE (), FALSE () )
Create a new table to make visual.
FinalTable = CALCULATETABLE ( Merge1, Merge1[Column] = TRUE () )
Add a new measure “TotalSales”.
TotalSales = CALCULATE ( SUM ( 'FinalTable'[Sales] ), ALLEXCEPT ( 'FinalTable', 'FinalTable'[Targets.YM], 'FinalTable'[Product Group] ) )
According to my ideas to get the results you want.
Best Regards,
Eads
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
98 | |
81 | |
65 | |
62 |
User | Count |
---|---|
147 | |
116 | |
104 | |
88 | |
65 |