Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

VAR formula error

Hi Experts

 

I am trying to write the following VAR formula but keep on getting an error, cannot see the wood for the trees.... HELP

 

LocationCount =
VAR Final_Total = IF(CALCULATE(COUNTA(Case_Data[Location])=BLANK(),(CALCULATE(COUNTA(Case_Data[Location])))
VAR Current_Category = CALCULATE(SELECTEDVALUE(Case_Data[Location]),
ALLEXCEPT(Case_Data, Case_Data[Location]))
Return

IF(Current_Category = BLANK(),FORMAT(Final_Total), FORMAT(Final_Total, "0"))

Need Help
  • sdjensen's avatar
    sdjensen
    8 years ago

    There is several errors in your formula - parenthesis in wrong places, and your first FORMAT is missing a second parameter. I have cleaned the parenthesis, but you still need to change your FORMAT to what ever you are trying to do.

     

    LocationCounts =
VAR Final_Total = 
IF(
	CALCULATE( COUNTA(Case_Data[Location]) ) = BLANK(),
	0,
	CALCULATE( COUNTA(Case_Data[Location]) )
)
VAR Current_Category = 
CALCULATE(
	SELECTEDVALUE(Case_Data[Location]),
	ALLEXCEPT(Case_Data, Case_Data[Location])
)
RETURN

IF(
	Current_Category = BLANK(),
	FORMAT(Final_Total), 
	FORMAT(Final_Total, "0")
)

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    hi,

    I think you miss a closing parenthesis at the end of the first VAR 

     

    Barna

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi Barna

       

      I worked that out and now have....

       

      still erroring

       

      LocationCounts =
      VAR Final_Total = IF(CALCULATE(COUNTA(Case_Data[Location])=BLANK(),0,(CALCULATE(COUNTA(Case_Data[Location]))))
      VAR Current_Category = CALCULATE(SELECTEDVALUE(Case_Data[Location]),
      ALLEXCEPT(Case_Data, Case_Data[Location]))
      RETURN

      IF(Current_Category = BLANK(),FORMAT(Final_Total), FORMAT(Final_Total, "0"))

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Could you provide a small sample dataset with the columns and datatypes you are using with this calc? So that I can have a closer look on that.