topic Grand Total not correct in DAX Commands and Tips

Grand Total not correct

HDavila — Mon, 27 Apr 2020 02:17:32 GMT

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.

PR TCE Cal	PR Net Revenue	PR Net Expense
$23,004	$713,100.28	$0.00
$23,004	$713,100.28	$0.00
$14,298	$443,290.07	$69.05

PBI Cal	$1,814,048.59	$83.24
Correct Cal	$1,869,490.63	$69.05

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.

Have you encountered this problem before? Can you help me troubleshoot this?

Anonymous

Re: Grand Total not correct

AlB — Mon, 27 Apr 2020 09:10:11 GMT

Hi HDavila

You'd need to show the code of your measures at the very least. Ideally share a pbix that reproduces the issue

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers

Re: Grand Total not correct

HDavila — Mon, 27 Apr 2020 13:10:54 GMT

Hi AlB ,

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

Hopefully, this helps.