Calculate a function as long as you have value for a certain variable

[Mojtaba]

I have a below function

Potential Saving calculation = ([Expected Usage]-[Actual Usage])*AVERAGE(Forecast_Table[Price])

If there is no value for Actual Usage the whole formula produses wrong result

I just want to tell power bi: calculate this as long as you have value for Actual Usage.

I have same problem here as well

Forecast Accuracy = ABS(ABS((SUM(Actual_Table[Expected_Usage])-[Actual Usage])) / [Actual Usage]*100 -100)

I just want to tell power bi: calculate forcast accuracy as long as or up until you have value for Actual Usage.

[Anonymous]

Hi @Mojtaba ,

Try adding "+0" after [Actual Usage].

Potential Saving calculation = ([Expected Usage]-([Actual Usage]+0))*AVERAGE(Forecast_Table[Price])

Best Regards,

Jay

[Mojtaba]

I tried but no changes happened

I just copy pasted your code please see below  screen shot

still takes in account expected usage when there is no actual usage

instead of showing only 95k it shows 981K

[TheoC]

Hi @Mojtaba 

I am really sorry but without more detailed information and the model, I won't be able to assist.  If you are able to share, I am sure that a resolution can be created. It is just a bit difficult making assumptions, etc., on the data types, relationships, filters, etc., that are not transparent through the screenshots, etc.

I trust you understand.

Theo

[Mojtaba]

Could it be because this is how I calculated actual usage?

Actual Usage = SUMX(DISTINCT(Actual_Table[Month]), FIRSTNONBLANK(Actual_Table[Actual_Usage], 0))

[TheoC]

@Mojtaba I'm unsure what your Actual Usage measure in the current state is aiming to achieve but if you are just wanting to SUM the value of your [Actual_Usage] column, I would highly recommend just using SUM. For example:

TotalActualUsage = SUM ( 'Actual_Table'[Actual Usage]).  

[Mojtaba]

I did this to sum only unique value for each month.For some reason ,not sure why.I had repetitive number in a month

[TheoC]

@Mojtaba The reason that this may have occurred is because of the format that your date columns (i.e. Month Year) are currently structured.  It is likely they were sitting as "text" and not connected reflective of a "date".  I believe it would be of material benefor for you to add a Data table that acts as the "source of truth" when you're using dates to slice and filter your data.  I highly recommend you add a Date table and use that as the basis of filtering, axis, etc for your report. You can get this from here: https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/).

[Mojtaba]

what info you need?

snap shots of table relationships?

and field parts?

[TheoC]

@Mojtaba yes please. An example of data used, a snapshot of your relationships, etc. Thank you.

[Mojtaba]

relaationshiptable 1table 2fields

[TheoC]

Hi @Mojtaba 

For the first measure, you could try something such as the following:

Potential Saving Calculation = 

VAR _PotentialSaving = ( [Expected Usage] - [Actual Usage] ) * AVERAGE ( Forecast_Table [Price] )

RETURN 

IF ( ISBLANK ( [Actual Usage] ) , "" , _PotentialSaving ) 

For the second measure, try the same approach:

Forecast Accuracy = 

VAR _ForecastAccuracy = ABS ( ABS ( ( SUM ( Actual_Table [Expected_Usage] ) - [Actual Usage] ) ) / [Actual Usage] * 100 -100 )

RETURN 

IF ( ISBLANK ( [Actual Usage] ) , "" , _ForecastAccuracy) 

Hope this helps!