How to show zero for blank in a total count

[lardo5150]

I have the below matrix

I am pulling from an excel spreadsheet that was exported from our tool.

The spreadsheet lists out engineer and the case number they have that is still open.

I am able to get a total for each engineer, as you can see above.

My problem is for people who do NOT have cases, towards the bottom.

When looking at the data, the cell just says "blank"

I tried to transform data and do a replace "blank" with null

I then in my formula tried to use BLANK function:

If(ISBLANK(IF(HASONEVALUE('OpenCases'[OwnerEmail]), CALCULATE(COUNT('OpenCases'[ParatureTicketNumber])+0,ALLSELECTED('OpenCases'[OwnerEmail]),'OpenCases'[ParatureTicketNumber]<> 0), BLANK())),0,(IF(HASONEVALUE('OpenCases'[OwnerEmail]), CALCULATE(COUNT('OpenCases'[ParatureTicketNumber])+0,ALLSELECTED('OpenCases'[OwnerEmail]),'OpenCases'[ParatureTicketNumber]<> 0), BLANK())))

I get the total for each engineer, but it still thinks that "null" is a value, so it adds a 1 for that engineer, instead of 0.

So how do I adjust this, so it is doing what it does right now, getting the total amount of cases per engineer, but then for the ones that have "blank" in the cell (or we can leave it null, whatever), how do I count these as 0 instead of 1.

[Anonymous · Accepted solution]

Hi @lardo5150 ,

You can create one measure as below if your table structure is same with the one in below screenshot:

Measure = CALCULATE(count('OpenCases'[ParatureTicketNumber])-COUNTBLANK('OpenCases'[ParatureTicketNumber]),ALLEXCEPT('OpenCases','OpenCases'[OwnerEmail]))

If the above measure is not applicable in your scenario, please provide sample data in table OpenCases (exclude sensitive data).

Best Regards

Rena

[amitchandak]

One you have add this option

You might have use something like sumx(table, if([value]+0 = 0 ,1,0))

[vivran22]

Hello @lardo5150 

If you had shared the sample data file, it would have been simpler to provide the solution.

I have recreated the data sample for this pupose:

Created the following meausres after replacing "blank" with null is Power Query:

Count Tickets = COUNTA(dtTickets[Case#]) - COUNTBLANK(dtTickets[Case#])

Open Tickets = CALCULATE([Count Tickets],KEEPFILTERS(dtTickets[Status] = "Open"))

Getting the following result

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn