DAX Calculate is calculating wrong lines

[bunkertrust]

Hi 

I am trying to calculate different fuel quantity losses using the DAX calculate. Problem is that it is also calculating the lines that are not relevant for the specific fuel type.

We have IFO, MGO, LSGO and ULSFO/VLSFO

Ex. the IFO loss calculation is: 

CALCULATE(SUM('bunkertrust_com_db vBunkerOperationsFirstView'[bunker_lsgo_received_vessel_figure]) - CALCULATE(SUM('bunkertrust_com_db vBunkerOperationsFirstView'[bunker_lsgo_departure_bdn])))

But it is also adding the calculation for the other fuel types.

Another issue is how i get the IFO loss % only to divide if the denominator or nominator has a number otherwise come blank. 

I hope you can help.

[Anonymous]

@bunkertrust 

for Loss %, you can simply use ISNUMBER() DAX function to check whether a number is valid or not. If condition satisfies, you can use DIVIDE() function to calculate final loss %

[Anonymous]

In your calculate dax you have not added any filter.
The expression should be like
Calculate(sum(table[amount]),filter(table,table[type]="IFO"))

THANKS
PRAVIN

If it resolves your problem mark it as a solution and give Kudos.

[bunkertrust]

Hi Pravin,

So you mean: 

IFO Loss MT = calculate(SUM('bunkertrust_com_db vBunkerOperationsFirstView'[bunker_ifo_received_vessel_figure]), FILTER('bunkertrust_com_db vBunkerOperationsFirstView', 'bunkertrust_com_db vBunkerOperationsFirstView'[bunker_ifo_received_vessel_figure]="IFO")) - CALCULATE(SUM('bunkertrust_com_db vBunkerOperationsFirstView'[bunker_ifo_departure_bdn]))))

This comes with syntax is incorrect

[Anonymous]

You have added to many parenthesis.

try this

IFO Loss MT = calculate(SUM('bunkertrust_com_db vBunkerOperationsFirstView'[bunker_ifo_received_vessel_figure]), FILTER('bunkertrust_com_db vBunkerOperationsFirstView', 'bunkertrust_com_db vBunkerOperationsFirstView'[bunker_ifo_received_vessel_figure]="IFO")) - CALCULATE(SUM('bunkertrust_com_db vBunkerOperationsFirstView'[bunker_ifo_departure_bdn]))

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Anonymous]

share some sample dataset and expected output so that it will be easy for me to answer your quetion.

Thanks,

Pravin

[bunkertrust]

@Anonymous 

Can you use this ?

Column A to H are the calculations : https://docs.google.com/spreadsheets/d/1igSUU-_7fRzllGukzsVVJ4kOg8elB3b_q0Y-Z54hH4Q/edit?usp=sharing

[bunkertrust]

Which format do you prefer?

[bunkertrust]

Hi Pravin,

This is coming with circular dependency in stead. 

The figures are coming in separated columns, so I suppose it is a matter of not calculating if blank?