Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Card visualization to show 0 instead of (Blank)

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!

 

DateCodeItems
03/01/2021FS3
03/01/2021FS6
04/01/2021FS2
04/01/2021PO9
04/01/2021RO5
04/01/2021RO2
05/01/2021RO7
05/01/2021RO3
08/01/2021AD6
08/01/2021AD3
08/01/2021AD2
08/01/2021AD3
08/01/2021WO4
08/01/2021WO7
10/01/2021FP8
10/01/2021FP8
11/01/2021FP4
11/01/2021PO3
11/01/2021PO2
11/01/2021PO10
13/01/2021PO6
13/01/2021WO3
13/01/2021WO3
13/01/2021WO7
13/01/2021WR2
14/01/2021GS9
14/01/2021PD4
14/01/2021PD11
14/01/2021PO8
14/01/2021PO5
15/01/2021GS3
17/01/2021GS3
18/01/2021WR5
18/01/2021WR1
19/01/2021FT6
20/01/2021GF12
20/01/2021GF3
1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

@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.

3.png1.png2.png

View solution in original post

18 REPLIES 18
tamerj1
Super User
Super User

@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.

3.png1.png2.png

Anonymous
Not applicable

@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.

Anonymous
Not applicable

@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??

MakeItReal_0-1658181068861.png

 

@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.

1.png

Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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

Currently, it is showing:

MakeItReal_0-1658104493525.png

But i want it to show:

MakeItReal_1-1658104508320.png

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@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

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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

MakeItReal_0-1658159623150.png

 

Sorry about that.  Try this measure

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

When you select GF, the result will be 15.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@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?

MakeItReal_0-1658228836664.png

 

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
tamerj1
Super User
Super User

Hi @Anonymous 

you can create a measure 

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

Anonymous
Not applicable

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

@Anonymous 

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

Anonymous
Not applicable

@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?

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.