Forum Discussion

mape's avatar
mape
Frequent Visitor
8 years ago

In a matrix, how I can replace values in empty cells with values of previous cells ?

Hi everyone!! I need help about this...   Measure of sales per year and in case there is no information for that year, show the sales of the previous year.   That is, given a series of sales per ...
Abduvali's avatar
Abduvali
Skilled Sharer
8 years ago

Hi mape,

 

 

You can create a Column with IF statement in it like:

 

  • COLUMN = if(YourTable[Sales] = BLANK(), 0, SUM(YourTable[SALES])
  • and then use that column for to display sales this should solve having blank cells in a matrix

 

 

Regards

Abduvali

  • mape's avatar
    mape
    Frequent Visitor
    8 years ago

    Hi Abduvali, thanks for you quick response, in fact the result isn't as I expected. 

     

    By default with the field "Sales" I have this  matrix:

     

     201520162017
        
    Customer A        900,00 €    1.200,00 €    1.000,00 €
    Customer B        700,00 €        500,00 € 

     

    The new measure or calculated column that I want , it shows like this:

     

     201520162017
        
    Customer A        900,00 €    1.200,00 €    1.000,00 €
    Customer B        700,00 €        500,00 €        500,00 €

     

    i.e., if cell is blank it could show the "last sales" in the previous just period

     

    If you can give me another idea, thanks...

    :)

    • Abduvali's avatar
      Abduvali
      Skilled Sharer
      8 years ago

      mape,

       

      That was tricky =D

       

      This should work for you just create a new column:

      • lastyear = 

        var l = SUM(Sheet6[Sales])
        var c = CALCULATE(SUM(Sheet6[Sales]),FILTER(ALLSELECTED(Sheet6[End].[Year]),Sheet6[End].[Year]-1))

        return
        if(l=BLANK(),c,l)

      Works like a charm =D

      Regards

      Abduvali

      • mape's avatar
        mape
        Frequent Visitor
        8 years ago

        Hi Abduvali,

         

        I have tested it with steps you say in your response but I don't get the same results.

         

        Mi new calculate column is:

         

        lastyear =
        var l = SUM(Ventas[ImporteVenta])
        var c = CALCULATE(SUM(Ventas[ImporteVenta]);FILTER(ALLSELECTED(Ventas[Ejercicio]);Ventas[Ejercicio]-1))
        return
        if(l=BLANK();c;l)

         

        Look the result

         

        Nombre20162017Total
        Customer1370013700174002
        Customer2370013700174002
        Customer3370013700174002
        Customer4370013700174002
        Customer537001 37001
        Customer6370013700174002
        Customer737001 37001
        Customer8370013700174002
        Total296008222006518014

         

        All cells shows the sum and for the cell of Customer5 and Customer7 with year 2017 these cells are empty.

         

         

        it is possible that since there is no value in the table, it will not show anything in those cells

         

        The original Sales table is:

        NumVentaEjercicioClienteImporteVenta
        12016C11500
        22017C1500
        32016C22000
        42017C22000
        52016C37500
        62017C32500
        72016C4600
        82017C4600
        92016C5450
        102017C62100
        112016C61100
        122016C72650
        132016C89001
        142017C84500

         

        thanks

        best regards