Forum Discussion
New Column based upon other two columns from two different tables by a category
Every month the number of animals fed per category is updated on this table
then I have this other table with fixed values that represent the total number of animals in the USA. These two tables are connected by "Categories". On the table above I want to have a column that represents the % of animas that the company feed based on the US Inventory for each category. How can I do that? I have tried calculate with divide function using sumx, etc... but no sucess. Suggestions please.
- Question why do you want to add it into the table. Many new users try to force an excel paradigm. Note if you do this it will only be calculated once when you load the data and any filters you add to your report won’t affect the results. May be fine if that’s what you want but likely you do t need it.
I would recommend a measure to calculate this and building a matrix or table visual to view your results.
The measure for a calculated column in table 2 would be something
pct of total = Divide( [col from table2],RELATED(Table1[table2column])
You really dont need the metric conversion for the lbs of feed etc either
If you had The following measure
Feed Amount lbs = sum[lbs of feed]
Feed Amount kgs = [Feed Amount lbs]*.454 gusdcruz in general you have data in tables along with the the lookup and bidge tables you need and then build meausres you need along with visuals to display the results. Sometimes calcualted tables and columns come in handy but in general try to avoid them when possible. Measures are calcualted only when needed and columns are calculated for every row. With large data sets this can be very inneficient and in most cases is not necessary. Plus doing it in measures and visuals lets PowerBI do the work for you in context.
3 Replies
- Question why do you want to add it into the table. Many new users try to force an excel paradigm. Note if you do this it will only be calculated once when you load the data and any filters you add to your report won’t affect the results. May be fine if that’s what you want but likely you do t need it.
I would recommend a measure to calculate this and building a matrix or table visual to view your results.
The measure for a calculated column in table 2 would be something
pct of total = Divide( [col from table2],RELATED(Table1[table2column])
You really dont need the metric conversion for the lbs of feed etc either
If you had The following measure
Feed Amount lbs = sum[lbs of feed]
Feed Amount kgs = [Feed Amount lbs]*.454Seward12533, do you recommend me creating a new table with this new measure? I agree that I am forcing an Excel approach here, and you are correct I am a new user ;)
thank you for your time
gusdcruz in general you have data in tables along with the the lookup and bidge tables you need and then build meausres you need along with visuals to display the results. Sometimes calcualted tables and columns come in handy but in general try to avoid them when possible. Measures are calcualted only when needed and columns are calculated for every row. With large data sets this can be very inneficient and in most cases is not necessary. Plus doing it in measures and visuals lets PowerBI do the work for you in context.
