Grand Totals numbers not correct

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.

image

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]

Can anyone please share some light on this issue?

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.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

Welcome to the forum
Would you be able to upload the pbix file please so can have a look?
Thanks
Najah

Also could you add as seperate measures in the table shown above the variables that your calculating.

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]
)

Thanks
Jarrett

The data I’m using is sensitive data from my company and I’m not allowed to share it but, this is the DAX code I used for the PR Net Rev

PR Net Revenue =

[PR Freight] + [PR TCO Hire] - [PR Address Comm] - [PR Broker Comm] + [PR Demurrage] + [PR Other Revenue] - [PR Offhire and Adj]

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) :

PR Freight =

VAR TotalFreight =

SUMX(‘VPR Raw Data’,‘VPR Raw Data’[Freight])

Return

VAR TotalVoyageDays =

SUMX(‘VPR Raw Data’,‘VPR Raw Data’[Voyage Days])

Return

DIVIDE(TotalFreight,TotalVoyageDays) * [Prorated days]

For Prorated days:

Prorated days = VALUE(SUMX(‘VPR Raw Data’,‘VPR Raw Data’[Prorated End date] -‘VPR Raw Data’[Prorated Start Date]))

Hi Ojones,

Can you expand on how I can use the SUMMARIZE function?

This is the DAX code I used for the Net Rev

PR Net Revenue =

[PR Freight] + [PR TCO Hire] - [PR Address Comm] - [PR Broker Comm] + [PR Demurrage] + [PR Other Revenue] - [PR Offhire and Adj]

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) :

PR Freight =

VAR TotalFreight =

SUMX(‘VPR Raw Data’,‘VPR Raw Data’[Freight])

Return

VAR TotalVoyageDays =

SUMX(‘VPR Raw Data’,‘VPR Raw Data’[Voyage Days])

Return

DIVIDE(TotalFreight,TotalVoyageDays) * [Prorated days]

For Prorated days:

Prorated days = VALUE(SUMX(‘VPR Raw Data’,‘VPR Raw Data’[Prorated End date] -‘VPR Raw Data’[Prorated Start Date]))

Hector,

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.

https://forum.enterprisedna.co/t/fixing-complex-total-errors-dax-formula-concepts/598

https://forum.enterprisedna.co/t/fixing-errors-with-your-totals-efficiently/4948

Thanks
Jarrett

Thank you very much, Jarrett (@JarrettM),

The videos were very informative. I fixed the issue using the following code:

PR Freight =
SUMX(SUMMARIZE(VALUES(‘VPR Raw Data’),‘VPR Raw Data’[Vessel/Voy],
“Freight”,SUM(‘VPR Raw Data’[Freight]),
“Voyage days”, SUM(‘VPR Raw Data’[Voyage Days])),
DIVIDE([Freight],[Voyage days]) * [Prorated days])

This solved my total issues for each one of my measures.

Thank you again.

Hector

1 Like