Card visualization to show 0 instead of (Blank)

[Anonymous]

Hello,

I have the following table with the list of codes updated every day and different codes could appear or disappear. I am trying to show the total number of items for a code which is currently not in the table. The code is "IT". I used a card visualization but the problem is that if i use the filter pane and set the Code is IT, then the card visualization shows (Blank) instead of 0. How to fix this? Maybe using a measure? Any help is much appreciated!

Date	Code	Items
03/01/2021	FS	3
03/01/2021	FS	6
04/01/2021	FS	2
04/01/2021	PO	9
04/01/2021	RO	5
04/01/2021	RO	2
05/01/2021	RO	7
05/01/2021	RO	3
08/01/2021	AD	6
08/01/2021	AD	3
08/01/2021	AD	2
08/01/2021	AD	3
08/01/2021	WO	4
08/01/2021	WO	7
10/01/2021	FP	8
10/01/2021	FP	8
11/01/2021	FP	4
11/01/2021	PO	3
11/01/2021	PO	2
11/01/2021	PO	10
13/01/2021	PO	6
13/01/2021	WO	3
13/01/2021	WO	3
13/01/2021	WO	7
13/01/2021	WR	2
14/01/2021	GS	9
14/01/2021	PD	4
14/01/2021	PD	11
14/01/2021	PO	8
14/01/2021	PO	5
15/01/2021	GS	3
17/01/2021	GS	3
18/01/2021	WR	5
18/01/2021	WR	1
19/01/2021	FT	6
20/01/2021	GF	12
20/01/2021	GF	3

[tamerj1 · Accepted solution]

@Anonymous 
Here is a sample file with the proposed solution https://www.dropbox.com/t/179dQf9YF6YLXGfo
Please let me know if this covers your requirement.

[Anonymous]

@tamerj1 Thanks for the solution! I have a follow-up related question: is it possible to group some of the codes and show the total/sum in a measure for each group?

Code groups:
Fun (FF, FP, FS & FT)
Tech (TC, TN, TD & TM)
Notes (AD, AF, AS, PE & CC)
Sound (WO, WR & WT)
Reboot (RO)
Restart (IT)
Hidden (PP)
New (PD,PO & PX)
Archive (GF & GS)

For example, a measure for "Fun" should include the sum of items which correspond to codes FF, FP, FS & FT.

[tamerj1]

@Anonymous 
Here you go https://www.dropbox.com/t/AnLtpFShl4pd7zJU

[Anonymous]

@tamerj1 I selected only one group at a time, for example, "Sound" but the total is not correct. I also checked a few other groups and the sum shown in the measure is higher than the actual sum.

Below, the codes for "Sound" have been selected and the total should be: 3+4+7+7+1+2+5=29 but the measure shows 32??

[tamerj1]

@Anonymous 
Actually the total (32) is correct. Don't be fooled by the summary table as the 13/01/2021 - WO - 3 record is duplicated therefore it will be shown only one time in the summary table unless you summaries Items column by sum.

[Ashish_Mathur]

Hi,

Please show the expected result that you want to see in the card visual for code IT.

[Anonymous]

@Ashish_Mathur Hi, the expected result would be in a card visualization. 

Currently, it is showing:

But i want it to show:

[Ashish_Mathur]

Hi,

Please also share the master list of all codes in a format that can be pasted in an MS Excel file.

[Anonymous]

@Ashish_Mathur Here is the master list:

FF

FP

FS

FT

TC

TN

TD

TM

AD

AF

AS

PE

CC

WO

WR

WT

RO

IT

PP

PD

PO

PX

GF

GS

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

@Ashish_Mathur There appears to be a problem, since any selection in the slicer gives zero value in the Card visualization.

[Ashish_Mathur]

Sorry about that.  Try this measure

Measure = coalesce(SUM(Data[Items]),0)

When you select GF, the result will be 15.

Hope this helps.

[Anonymous]

@Ashish_Mathur That measure was already in your PBIX file. It works for the group Archive, which comprises of codes GF and GS - the total is correct. But it does not work for several other groups, for example, the group Fun has codes FF, FP, FS and FT, but the total in the measure is not correct. It should be 29 instead of 37. Any idea what could be wrong?

[Ashish_Mathur]

Hi,

No, i do not.  But i believe you already have a solution from another contributor in this thread. 

[tamerj1]

Hi @Anonymous 

you can create a measure 

Tota Items =
COALESCE ( SUM ( TablName[Items] ), 0 )

[Anonymous]

@tamerj1 Thanks for your reply! But i need the sum for code = IT. How to do this?

[tamerj1]

@Anonymous 

but you said the code IT is missing from the table. Wiuld yoy please clarify further?

[Anonymous]

@tamerj1 I am trying to show the total number of items for a code which is currently not in the table. The code is "IT" (without quotes). I used a card visualization but the problem is that it displays "(Blank)". How to make it show "0" instead?