Forum Discussion
Create a Calculated Column from Different Tables
Based on my understanding, it depends on the relationship.
If 1:1, below formula should work.
calculated column = table1[X] - RELATED ( table2[Y] )
If 1:many, the following formula should work.
calculated column = oneTable[X] - CALCULATE ( SUM ( manyTable[Y] ), ALLEXCEPT ( manyTable, Table10[linkedCol] ) )
Based on my understanding, it depends on the relationship.
If 1:1, below formula should work.
calculated column = table1[X] - RELATED ( table2[Y] )
If 1:many, the following formula should work.
calculated column =
oneTable[X]
- CALCULATE ( SUM ( manyTable[Y] ), ALLEXCEPT ( manyTable, Table10[linkedCol] ) )Hi,
I am new to Power BI . Can I use data in tables to calculate more data?
My example is: I have Forecasted Product sales for 2 weeks. Prod 1, 100 units, Prod 2, 200 units.
I have a table with the Sales Mix % by Week, Week 1, 75%, week 2, 25%
Can I calculate out these to two table to create a new table with forecasted sales by week.
eg
Week1, Prod1, 75 units
Week2, Prod1, 25 units
Week1, Prod2, 150 units
Week2, Prod2, 50 units
I am calculating this in excel and then loading to power BI.
Thanks
Sam
I'm trying to do a similar thing but can't get the formula you provided to work.
I have two tables and want to subtract Column A from Table 1 from Column B from Table 2. There is a relationship between the two tables via a common ID.
The relationship is one to many whereby the value being subtracted is the 'one table' and the one from which data is being subtracted from, is the 'many table'.
The data is in date format.
i.e 05/03/2017 - 03/03/2017 = 2
Any help appreciated.
Thanks
Hi,
Share the link from where i can download your file.
Hi ,
i am trying to create a calculated column using 2 different tables. i am getting error.
My query:
=if(table1[column1] = "abc" || table1[column1] = "bcd", "Others", if( table1[column1] ="xyz" , if( table2[column2] = "pqr", "Oman", if( table2[column2] = "que", "hyz", "ecc"))))
Error:
column 'column2' in 'table2' cannot be found or may not be used in this expression.
please suggest proper syntax.
thank you.
What if it's a Many to many relationship?
We have two tables with a relationship of many : 1 between Table1and Table2.
We are getting errors for below derive column for filter condition : Table1[Web Activity Date] <= RELATED(Table2[Lead Created Date])------------------
# Distinct Forms Filled by Net New Lead = CALCULATE(DISTINCTCOUNT(Table1[Form ID]),v_rpt_omniture[Post Visid High Low] <> "10"
&& Table1[Form Completions] = "1"
&& Table1hit Source] = "1"
&& Table1[Exclude Hit] = "0"
&& Table1[Web Activity Date] <= RELATED(Table2d[Lead Created Date]))Hi,
In Table1, write the RELATED function as a calculated column formula and give a title to that column as Lead date. Write another calculated column column called test with this formula =Table1[Web Activity Date]<=Table1[Lead date] . Revise your measure to:
# Distinct Forms Filled by Net New Lead = CALCULATE(DISTINCTCOUNT(Table1[Form ID]),v_rpt_omniture[Post Visid High Low] <> "10"
&& Table1[Form Completions] = "1"
&& Table1hit Source] = "1"
&& Table1[Exclude Hit] = "0"
&& Table1[Test] = TRUE())Thank you Ashish_Mathur for the solution. It is helpful.
Just want to confirm : Are we saying we cannot use related table with multiple filter condition in CALCULATE function?
what is the Table 10 related to?
