Help with VAR statement (result)

[Anonymous]

jj

I am trying to add another option to my "return" statement below.   I have the answer for if the first VAR statement is true...now I need show what the result be if the second VAR statement is true.    How can add in what that formulae should be into my Return?      

Total Estimated Forecast =

VAR _Reconciled = FILTER('Net Revenue Table','Net Revenue Table'[Reconciled?] = "Yes")

VAR _NotReconciled = FILTER('Net Revenue Table','Net Revenue Table'[Reconciled?] = "No")

Return

CALCULATE([Sum of Net Revenue],_Reconciled)

[Anonymous · Accepted solution]

@CNENFRNL  @Anonymous  @Ashish_Mathur   @Samarth_18   Thanks all for your help.  In the end, I was able to get it to work with this which was a lot simpler than I thought.   

Net Revenue (not including current month) =

IF(SELECTEDVALUE('Net Revenue Table'[Reconciled?]) = "Yes",[Sum of Net Revenue],IF(SELECTEDVALUE('Net Revenue Table'[Reconciled?]) = "No","Pending",""))

[v-henryk-mstf]

Hi @Anonymous ,

Whether the advice given by @CNENFRNL@Anonymous  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

[Ashish_Mathur]

Hi,

Does this measure work

Measure = CALCULATE([Sum of Net Revenue],'Net Revenue Table'[Reconciled?] = "Yes") + CALCULATE([Your Other measure,'Net Revenue Table'[Reconciled?] = "No")

[Anonymous]

this is pseudocoded so you will need to put things together yourself based on naming conventions in your dataset; but I would recommend using an if statement.

RETURN
IF(_Reconciled = "Yes", Calculate([Sum of Net Revenue, _Reconciled), "Different Measure you need to use")

This statement makes the assumption that you are either Yes/No for reconciled, and there are no alternative options. If there are further options you would need to add a subsequent nested IF statement.

[CNENFRNL]

Either _Reconciled or _NotReconciled is NOT logical expression, but table expression. It returns a table, which evaluates to BLANK() or with rows. You can use ISEMPTY(_Reconciled) / ISEMPTY(_NotReconciled) in your subsequent steps.

[Samarth_18]

Hi @Anonymous ,

If I took your question correctly then you can try below options:-

1. If you wanted to sum both the result then you can try this:-

Total Estimated Forecast =

VAR _Reconciled = FILTER('Net Revenue Table','Net Revenue Table'[Reconciled?] = "Yes")
VAR _NotReconciled = FILTER('Net Revenue Table','Net Revenue Table'[Reconciled?] = "No")

Return
CALCULATE([Sum of Net Revenue],_Reconciled) + CALCULATE([Sum of Net Revenue],_NotReconciled)

2. If you want to show both the result on cards then you can try like:-

Total Estimated Forecast =
VAR _Reconciled =
    FILTER ( 'Net Revenue Table', 'Net Revenue Table'[Reconciled?] = "Yes" )
VAR _NotReconciled =
    FILTER ( 'Net Revenue Table', 'Net Revenue Table'[Reconciled?] = "No" )
RETURN
    "For Reconciled YES " & CALCULATE ( [Sum of Net Revenue], _Reconciled ) & " " & "For Reconciled YES "
        & CALCULATE ( [Sum of Net Revenue], _NotReconciled )

Or please share the expected output with examples.

Thanks,

Samarth

[Anonymous]

@Samarth_18   Hi Samarth  -  I am not looking to sum them together, but rather give a result depending on which VAR is true.    If the _reconciled is true it needs to return the sum of net revenue.   If it is not true (i.e. if _notreconciled is true, then I have a different measure I need to use.