Forum Discussion
Custom Calculated Table in POWER BI
I have data represented in the following manner.
Table A has Parent company name and overall sales and overall profit (this table was a manual table in POWER BI desktop and just had Parent company, child companies as columns)
Table B is calendar table
Table C is daily sales and daily profit for certain parent companies.
Table D is daily sales and daily profit for certain parent companies.
I created caculated columns in Table A for overall profit using IF and measures i created in Table C & Table D and overall sales and it works perfectly.
Now to do the monthly trending i wanted to show overall sales and used an article that would bring trend charts by month into the visual.
I drag parent company sales and month and the visual is perfectly calulating in a table visual.
How ever when i want to have this calculated in TABLE A or another calculated table I am hitting a hard wall.
I wish POWER BI gave us the ability to convert a table visual into a calculated table.
3 Replies
- HotChilli
Community Champion
SUMMARIZECOLUMNS is your friend here.
The code that powerbi uses is available to you through View->Performance Analyzer. Click on a new page in Report View. Start the analyzer. Click on to the page with the visual. Use Copy Query to capture the DAX. It will require tweaking to get what you want since it runs a catch-all algorithm to cover all scenarios (subtotals, total, sorting etc). You don't need to bother with that since you know exactly what you want.
Good luck
- Ashish_Mathur
Super User
Hi,
Share some data and show the expected result.
HI Anonymous,
Perhaps you can refer to the following blog about join table fields if it helps you to create a calculated table with multiple related table fields:
From SQL to DAX: Joining Tables
Regards,Xiaoxin Sheng
