Forum Discussion

igorabdo's avatar
igorabdo
Advocate II
7 years ago
Solved

last value with conditional (zero)

HI folks   I want to repeat the last value when the actual value is numeral zero. I tried to use with earlier, but unsuccessfully.     Tks Igor
Need Help
  • AlB's avatar
    AlB
    7 years ago

    Hi igorabdo

     

    If you want to have the average at the total there are two possibilities. I'm not sure which one you prefer.

    One is that we take the average with the original zeros (i.e. without the filled-in values). Note that we are using the [Mesure_CUSTO] defined previously. You don't need to use it in the visual any longer but do keep the definition.

     

    Measure_CUSTO_With_Avg@TotalWithOriginalZeros =
IF (
    ISFILTERED ( Table1[Mês Ano] );
    [Measure_CUSTO];
    AVERAGEX (
        ADDCOLUMNS (
            SUMMARIZE ( Table1; Table1[ITEMID]; Table1[Mês Ano] );
            "Result"; CALCULATE ( SUM ( Table1[CUSTO] ) )
        );
        [Result]
    )
)

     

     

igorabdo's avatar
igorabdo
Advocate II
7 years ago

Hi AlB

 

For example. In February/2018 the value is zero, but in January/2018 is 571,58. So, if is zero, I need to bring the last value (value <> 0)

AlB's avatar
AlB
Community Champion
7 years ago

igorabdo

 

I get it now but i need more details to be able to work it out.

Can you share the pbix? That would be the quickest. Perhaps just with some dummy data if you have confidential data in it.

Otherwise:

1. What you are showing is a table visual? What are ITEMID, CUSTO, INVENTITEID and Mes Ano? Columns in your data table or measures? Is Mes Ano of type date or text?

2. If CUSTO is a measure I need to see its code.

3. Can you show a sample of your table(s) (not the visual, the table(s) itself). So that we can see the columns in it