Forum Discussion

Rasmus7700's avatar
Rasmus7700
Frequent Visitor
2 years ago

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

  • Hi Rasmus7700 ,

     

    It's seem to be an issue with the relationships between the two tables. How are the tables related?

    • Rasmus7700's avatar
      Rasmus7700
      Frequent 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".... 

      • MFelix's avatar
        MFelix
        Super 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.