Forum Discussion

delithyotho's avatar
delithyotho
Frequent Visitor
4 years ago
Solved

Card Display $0 when rows are blank with logic

Hi Everyone,

 

I have a series of Card graphs that sums everything and has a measure that will automatically converts them into corresponding abbreviations like 1bn and 1M, though they will be converted as text after that. Here's the current set up:

 

Card graph =
Var Total1 = CALCULATE(SUM('MasterData'[Sum1])*1000000)
Var Total2 = CALCULATE(SUM('MasterData'[Sum2])*1000000)
Var decimal = "0.0"
RETURN
SWITCH ( TRUE() ,
Total2 >= 1000000000 , CONCATENATE("$", FORMAT (Total1/1000000000, decimal & "bn")) ,
Total2 < 1000000000, CONCATENATE("$", FORMAT (Total1/1000000, decimal & "M")) ,
CONCATENATE("$", FORMAT (COALESCE(Total1, 0) , decimal)
))
 
Basically, Total2 is the star of the show and the other cards follow its format, either it uses bn or M as abbreviation.
 
Here's the sample raw:
 
CountrySum1Sum2
Country123
Country246
Country3 9
Country4812
Country510 
Country61218
Country71421
Country8 24
Country91827
Country102030

 

i have a filter for the Country so that the card graphs highlight the particular country and the sum. 

 

With the current Measure, whenever a particular country is highlighted say Country8, it now shows only a "$" whenever there's blank.

It used to work a few months ago showing $0.0 whenever its blank. 

 

I would like to have this feature back whenever there's a blank entry, it will just show as $0.0

 

Please advise, thanks!

card
SUM based on existing context created by a measure
VAR
  • amitchandak's avatar
    amitchandak
    4 years ago

    delithyotho , Add COALESCE to the second condition too, and check. Or add +0 at the end

     

    Card graph =
    Var Total1 = CALCULATE(SUM('MasterData'[Sum1])*1000000)
    Var Total2 = CALCULATE(SUM('MasterData'[Sum2])*1000000)
    Var decimal = "0.0"
    RETURN
    SWITCH ( TRUE() ,
    Total2 >= 1000000000 , CONCATENATE("$", FORMAT (Total1/1000000000, decimal & "bn")) ,
    Total2 < 1000000000, CONCATENATE("$", FORMAT (COALESCE(Total1, 0)/1000000, decimal & "M")) ,
    CONCATENATE("$", FORMAT (COALESCE(Total1, 0) , decimal)
    ))

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    delithyotho , Add COALESCE to the second condition too, and check. Or add +0 at the end

     

    Card graph =
    Var Total1 = CALCULATE(SUM('MasterData'[Sum1])*1000000)
    Var Total2 = CALCULATE(SUM('MasterData'[Sum2])*1000000)
    Var decimal = "0.0"
    RETURN
    SWITCH ( TRUE() ,
    Total2 >= 1000000000 , CONCATENATE("$", FORMAT (Total1/1000000000, decimal & "bn")) ,
    Total2 < 1000000000, CONCATENATE("$", FORMAT (COALESCE(Total1, 0)/1000000, decimal & "M")) ,
    CONCATENATE("$", FORMAT (COALESCE(Total1, 0) , decimal)
    ))