Forum Discussion

shun888's avatar
shun888
Frequent Visitor
2 years ago
Solved

How to count all nonblank values in a matrix visual

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!!

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    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"))))

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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