How to count all nonblank values in a matrix visual

[shun888]

Hi, 

I have created a matrix visual similar to the following. With the first column A,B,C being the names. And the first row 21,22,23 the dates. The values is a measure that sums up how many burgers they have eaten - Calculate(sum('Food'[Eaten]), Filter('Food', [Type] ="Burger"))

Now I want to create a Scorecard that counts the total of nonblank values in the visual. In this case it would be 8 as there is only 1 cell that is blank in this 3x3 matrix. 

Any help would be appreciated thanks!!

[Anonymous · Accepted solution]

Hi @shun888 ,

If I understand correctly, the issue is that you want to count all nonblank values in a matrix visual. Please try the following methods and check if they can solve your problem:

1.Create the Food table. 

2.Move the Name field into the Rows, move the Date into the Columns and move the Eaten into the Values.

3.Create a measure that counts the total of nonblank values in the visual. Enter the following DAX formula.

Measure 2 = 
CALCULATE(
    SUM(Food[Eaten]),FILTER(Food,[Type] = "Burger")
)

4.Publish the report to the service.

5.Create a new scorecard.

6.Edit the Current value and Final target. Select the Connect to data.

7.Choose the Final target and select the Measure 2.

8.Select Save.

Alternatively, you can use the following link to learn how to create a scorecard.

Create scorecards and manual metrics - Power BI | Microsoft Learn

Create connected metrics in Power BI - Power BI | Microsoft Learn

Looking forward to your reply.

^{Best Regards,}

^Wisdom

[amitchandak]

@shun888, This measure can help

distinctcount(Table[Name]) * distinctcount(Table[Date]) -
Countrows(Summarize(Table, Table[Name], Table[Date], "_1", Calculate(sum('Food'[Eaten]), Filter('Food', [Type] ="Burger"))))