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:
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
Please advise, thanks!
Solved! Go to Solution.
@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)
))
It works! thank you!
@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)
))
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!