Forum Discussion
Values from two tables and filtering?
Hello Guys.
I'm trying to build a dashboard from Business Central data - and thouht i allmost was there - but then i hit the head to the wall again - hopefully you guys can help me out here...
I've got the "Job Table" from BC into PowerBI and done all the needed transformation to make that look ince and get all the filters i want to have - based on "Job Tasks", "Ressource Group" and so on - that actually looks OK.
Like This:
The Pivot Visualisation is showed based on the following:
- Rows:
- Job Number
- Line Type
- Ressource Group
- Work Type
- Job Number and Description
- Columns
- Amount
- Total Price
Then i have another table with the "Budget Lines" for the same job - also based on the "Job Tasks" as theese from the "Job Table". I tried to setup the "Budget Lines" table so i had all the same "Rows" as above - then i would like to add a "Amount" and "Total Price" for the "Columns" but it just takes the Sum based on the "JOb Number" - it's not using all the Rows for filtering like it does on the other table...
Following picture shows the third / fourth column that's just the same value - not based on the selection like the first / second column is....
If i take it the other way around and add the Columns from "Budget Lines" to the table "Jobs" i will get the same amount way too many times since there can be 1 budget line - and 100 realized lines in the other table....
What am i missing here?
5 Replies
- MFelixSuper User
Hi Rasmus7700 ,
It's seem to be an issue with the relationships between the two tables. How are the tables related?
- Rasmus7700Frequent Visitor
Mhhh.. Could be 🙂
The two tables only have relationship between the "Job Number" and "Job Number and Description" - not all the other fields shown in the Pivot as "Rows"....
- MFelixSuper User
Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.
If the information is sensitive please share it trough private message.
- AnonymousNot applicable
Hi Rasmus7700 ,
Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng