Forum Discussion

MPC's avatar
MPC
Regular Visitor
3 years ago

How to replace the blank with zero in Matrix visual

Hi Team,
amitchandak 
I have a dataset like below.

For the above data I have to show Percentages.

Requirement Is : there is no data for some zones, instead of Blank I have to show 0%.

DAX I have written is a as follows:

Percentage of project =
var a= CALCULATE(COUNT(Sheet2[Project]))
var b=CALCULATE(COUNT(Sheet2[Project]),ALL(Sheet2[Zone]))
return
IF((a/b)=BLANK(),0,(a/b) )+0
 
Please check and suggest me a suitable answer.
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi MPC,

    If these blank part records include in your table, you can simply use 'replace value' function on 'query editor' side to replace null values to zero to achieve your requirement.
    If this blank mean not existed match values, you may need to create an unconnected table with all correspond category and use on matrix. Then you can write a measure formula to check the current row and column value to lookup raw table records and add +0 after the expression to handle not existed parts to zero.
    Regards,

    Xiaoxin Sheng

    • MPC's avatar
      MPC
      Regular Visitor
      3 years ago

      This only not working 

      Percentage of project =
      var a= COUNT(Sheet2[Project])
      var b=CALCULATE(COUNT(Sheet2[Project]),ALL('Table'[Zone]))
      var c=a/b
      var d=SELECTEDVALUE(Sheet2[Zone])
      return
      IF(ISNONTEXT(d),0,c)+0
      Result:

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi MPC,

        Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

        How to Get Your Question Answered Quickly  

        Regards,

        Xiaoxin Sheng