Hi, I’ve been trying to calculate the grand totals but I don’t understand why it is not calculating the right amount.
Below you can see the table with the amounts that I have in different measures.
As you can see the total that PBI calculates is showing 83.24 when the correct amount for the net expense is 69.05. Because of this issue, I’m having incorrect grand totals for all my PR calculated measures. Now, the PR TCE Cal takes the total of PR Net revenue - PR Net expenses and then divides the total by the 31 days. I notice that the PR TCE Cal is correct per line but the total amount is incorrect since it is taking the incorrect totals of the revenue and the expenses.
This is the DAX code I’m using for the measures
PR TCO Hire =
VAR TotalTCOHire =
SUMX('VPR Raw Data','VPR Raw Data'[TCO Hire])
VAR TotalVoyageDays =
SUMX('VPR Raw Data','VPR Raw Data'[Voyage Days])
Return
DIVIDE(TotalTCOHire,TotalVoyageDays) * [Prorated days]
Hi @hldpr026, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.
Use the forum search to discover if your query has been asked before by another member.
When posting a topic with formula make sure that it is correctly formatted to preformated text </>.
Use the proper category that best describes your topic
Provide as much context to a question as possible.
Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.
Sounds like you need to use the SUMMARIZE function. Please post a sample OBIX file in the future in order to get a quicker response on the forum. Check out this video from Sam for the logic.
Here is a sample Measure that I use in one of my reports with this logic:
WIP Remain =
SUMX (
SUMMARIZE (
Job_Mgmt_Report,
Job_Mgmt_Report[Job #],
“Estimated Revenue”, [Estimated Rev Total],
“%Budget Remain”, [% Budget Remain#]
),
[Estimated Revenue] * [%Budget Remain]
)
Now for the individual measures, for example, for PR Freight this is the code (I use the same code for all measures the only difference is the column that is referenced on the first variance, in this example is the Freight column) :
Now for the individual measures, for example, for PR Freight this is the code (I use the same code for all measures the only difference is the column that is referenced on the first variance, in this example is the Freight column) :
Here a couple of links that will explain in detail the SUMMARIZE function. Sam has some great content on this function. This is the main function I use when I’m not getting the correct total for measures. Not saying it is the only way, but it is definitely my go to for this type of solution.
