Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Issue while converting Blanks to 0 in Matrix

Hi All,

 

I have two datasets : Table 1 & Table 2 by joining on Desig to get all the desig values and represent the data in Matrix. All Desig may not come from Table1 for a particular date so, we are maintain the Required Desig in table2 and by joining with table2 will get all the Desig's along with it implementing the custom sort as well.

Here, I have to calculate the delta of stage1/stage2/Stage3 from 1/2/2020 - stage1/stage2/Stage3 of 1/1/2020.

Due to the blanks in Stage3 in 1/1/2020, the negation/subtraction is not happening correctly. I tried to make the 0 using ISBlank with if condition, still nothing is happening

IF(ISBLANK([Column1]),0,[Column1]).

Column1 : is having the numeric value goes to Values in Matrix.

I tried to make 0 in delta calculations, but it is also not working.

I would like to get 0 instead of Blanks to calculations/ Or any another proposed solution to resolve this issue .

 

 

Thanks in advance.

  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi  Anonymous ,

     

    Based on your data,create a measure as below:

    Measure = 
var _countonJan1=CALCULATE(MAX('Table1'[Count]),FILTER(ALL(Table1),'Table1'[Date]=DATE(2020,1,1)&&'Table1'[Design]=MAX('Table1'[Design])))
var _countonFeb1=CALCULATE(MAX('Table1'[Count]),FILTER(ALL(Table1),'Table1'[Date]=DATE(2020,2,1)&&'Table1'[Design]=MAX('Table1'[Design])))
Return
_countonFeb1 -_countonJan1

    Then put in a matrix you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , you can do this month - last month

     

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))+0
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))+0
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))+0
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))+0
previous month value =  CALCULATE(sum('table'[total hours value]),previousmonth('Date'[Date]))+0

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])
  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    6 years ago

    Hi  Anonymous ,

     

    Based on your data,create a measure as below:

    Measure = 
var _countonJan1=CALCULATE(MAX('Table1'[Count]),FILTER(ALL(Table1),'Table1'[Date]=DATE(2020,1,1)&&'Table1'[Design]=MAX('Table1'[Design])))
var _countonFeb1=CALCULATE(MAX('Table1'[Count]),FILTER(ALL(Table1),'Table1'[Date]=DATE(2020,2,1)&&'Table1'[Design]=MAX('Table1'[Design])))
Return
_countonFeb1 -_countonJan1

    Then put in a matrix you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!