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
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 |
Solved! Go to 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
Here is a sample file with the proposed solution https://www.dropbox.com/t/179dQf9YF6YLXGfo
Please let me know if this covers your requirement.
@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 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??
@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.
Hi,
Please show the expected result that you want to see in the card visual for code IT.
@Ashish_Mathur Hi, the expected result would be in a card visualization.
Currently, it is showing:
But i want it to show:
Hi,
Please also share the master list of all codes in a format that can be pasted in an MS Excel file.
Hi,
You may download my PBI file from here.
Hope this helps.
@Ashish_Mathur There appears to be a problem, since any selection in the slicer gives zero value in the Card visualization.
Sorry about that. Try this measure
Measure = coalesce(SUM(Data[Items]),0)
When you select GF, the result will be 15.
Hope this helps.
@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?
Hi,
No, i do not. But i believe you already have a solution from another contributor in this thread.
Hi @Anonymous
you can create a measure
Tota Items =
COALESCE ( SUM ( TablName[Items] ), 0 )
@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?
@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?
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |