DAX measure from two different tables for Financial Template

[naomimgmt]

Hi, 

I'm having trouble creating a financial template on Power BI. I have seen a couple of videos on different methods but when it comes time to execute them I get errors. The method that I am using requires you to build a table that has the outline of your financial template like this: 

And I have another table that is my general ledger with my amounts for each specific Item ID. However, whenver I try to use this formula: 

Sales = CALCULATE (SUM(Jan[Value]), 'Financial Temp'[Item ID ]="Astar Job Income")

It calculates the sum of all values of January (January being my General Legder with numbers and descriptions), instead of just the Sales line item where Sales = "Astar Job Income". It completely ignores the rest of the formula and doesn't ideally take the sum value of Jan when the Item ID = "Astar Job Income". 

Please let me know if there is a different way to do this or if I'm missing something. 

[naomimgmt]

@123abc 

[naomimgmt]

Hi!

Thank you so much for the reply! A lot of the information that you gave me helped me out figure out a lot of the fundamental issues with my dataset/template.  

Things that I fixed:  

1. In my first sheet I noticed that I was missing quite a few categories or unique items from my General Ledger. So I went in coped the column with all the categories and deleted the duplicates. After being left with only the unique values, I organized them in my financial template.  
2. 
3. I also realized there was no relationship between the financial template and the general ledger. I needed at least a one to many relationship that connects both tables. So I merged as a query the general ledger and the financial template.  
4. My result after merging was a separate query sheet with an added column. In this column I selected only the Line Item (Unique ID) to be visible in that column. Basically in the financial template I have a column called line item that works as a unique number id, and when I merged the general ledger and financial template I populated the extra column with the unique number id in the general ledger. I circled it in red. 5.
5. When I checked the relationship the relationship tab, I was able to see that a one to many relationship formed.  
6. When I click on the line it shows a relationship between Daxtemp. Line Item (which is the number id in the genral that was populated after the merge) and Line item (number id I mentioned earlier)  

   BIG PICTURE: Line Item – connection – Dax temp.LineItem (Item ID in the general ledger) 

   

   STILL NEED HELP ON THE DAX AND THE FOLLOWING: 

   I tried using your formula but it gives me the following error 

   

   Not sure if the relationship is still not set up right. I also double checked for errors with capitalization and spelling. Pretty sure it has to do with the relationship but I'm not sure.  

   Would appreciate some more advice, 

   Thanks 😊  

[123abc]

It seems like you're facing an issue with your DAX formula in Power BI. The formula you provided should work correctly if your relationships between tables are set up properly. Here are a few things to check and consider:

1. Relationships:

   • Make sure there is a relationship between the 'Item ID' column in the 'Financial Temp' table and the 'Item ID' column in the 'Jan' table. You can create a relationship by going to the "Model" view in Power BI and dragging a line between the corresponding columns in both tables.

2. Context Transition:

   • The formula you provided should work in a context where the 'Financial Temp' table is active. Ensure that the context is set up correctly. You might want to use functions like RELATED to navigate through relationships.

3. Data Model:

   • Verify that your data model is set up correctly. Check the data types of the columns involved in relationships and make sure they match.

Here's an adjusted version of your formula that explicitly references the related column:

Sales = CALCULATE(
SUM(Jan[Value]),
RELATED('Financial Temp'[Item ID]) = "Astar Job Income"
)

This assumes that there's a direct relationship between the 'Item ID' columns in both tables.

If the issue persists, please provide more details about your data model, including the relationships between tables and the structure of your tables. Additionally, check if there are any filters or slicers affecting the context in which your formula is being evaluated.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.