Skip to main content

Notifications

Small and medium business | Business Central, N...
Suggested answer

PBI Matrix - how to display on the same column data from 2 different BC tables

(1) ShareShare
ReportReport
Posted on by 4

Both 'General Ledger' and 'Items Sales' are sourced from Business Central, and they are linked by [Document_No], but 'General Ledger' contains some [Document_No] that do not exist in 'Items Sales'.
'General Ledger' contains the actual sales under each GL account. 'Items Sales' contains the breakdown of items in some of the GL accounts.

 
Q1. I've created a matrix that displays the actual sales of items that have been grouped into the same names as the GL accounts, but the figures of the GL accounts that are not from sales of items are missing from the matrix because they are not in 'Items Sales'. Is there a way to display these figures on the same column as the sales of items? 
To make it more complicated, the GL accounts contain partly sales of items in 'Items Sales' and partly entries only in 'General Ledger'. I need the matrix to show actual sales of both the breakdown of items that are only in 'Items Sales' and the entries that are only in 'General Ledger'.
 
Q2. The [Posting_Date] in 'General Ledger' is the actual sales date, but the [Posting_Date] in 'Items Sales' are wrong. I've calculated the correct [Deferred Date] in 'Items Sales', but I can't link this [Deferred Date] to ‘Dates’ because ‘Dates’ is already linked to [Posting_Date] in 'General Ledger' and 'General Ledger' is linked to 'Items Sales' by [Document_No]. I can’t use 'Items Sales' [Deferred Date] instead of 'General Ledger' [Posting_Date] because 'Items Sales' do not contain all the entries in 'General Ledger'. Is there a way to display the sales of each item according to the correct date?
  • Suggested answer
    YUN ZHU Profile Picture
    YUN ZHU 76,948 Super User 2025 Season 1 on at
    PBI Matrix - how to display on the same column data from 2 different BC tables
    Hi, I'm not sure what Item Sales page you mentioned is.
    If you have difficulty processing data in Power Bi, you can customize a new page in BC and combine some data from code first. Then do the processing in Power BI. This will be much easier.
     
    Hope this can give you some hints.
     
     
    Thanks.
    ZHU

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,979 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,848 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans