Forum Discussion

dogburalHK82's avatar
dogburalHK82
Icon for Helper III rankHelper III
3 years ago

calculation percentage with blank

Hi, 

 

When calculating points from given matrix, it gives non value (or blank) when the lookup value is blank. 

In fact, the value should be 0%,  not blank. 

 

example as below. 

 

To calculate score against Late % matrix, the score for Feb is not shown as the Late% calculated is zero, but recognised as blank.

File is uploaded here as well. 

https://filebin.net/6nyv2yhxq7f5hgdv  

Need Help

4 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago

    To report on things that are not there you need to use disconnected tables and/or crossjoins

  • dogburalHK82's avatar
    dogburalHK82
    Icon for Helper III rankHelper III
    3 years ago

    lbendlin would you be able to explain with the example? I do not quite understand. 

     

    So far,  i tried below

     

    COALESCE dax

    or 

    _early =
    var _blank = calculate(countA(Table1[Item Number]), filter(Table1, (Table1[days diff])<(-early[early Value])))
    return
    if(_blank = blank(), 0, _blank)
     
    but then they shows all months.. and score table only still shows Jan.

     


     

     

      • dogburalHK82's avatar
        dogburalHK82
        Icon for Helper III rankHelper III
        3 years ago

        thanks lbendlin 

        I did not know this trick. 

         

        However, i still do see unwanted months in the table. how can I get rid of those months? 

         

        When I tried below, it seems working. 

        avg_early = averagex(Summarize('Table1', 'Table1'[Order Date]), calculate([_early]))

         

         

         

        but not working with this

        avg_late = averagex(Summarize('Table1', 'Table1'[Order Date]), calculate([score_late]))