cancel
Showing results for
Did you mean: Frequent Visitor

## 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:

 Country Sum1 Sum2 Country1 2 3 Country2 4 6 Country3 9 Country4 8 12 Country5 10 Country6 12 18 Country7 14 21 Country8 24 Country9 18 27 Country10 20 30

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

1 ACCEPTED SOLUTION  Super User

@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 2 Frequent Visitor

It works! thank you!  Super User

@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)
)) Announcements #### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day! #### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference. Top Solution Authors
Top Kudoed Authors
Users online (3,364)