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

It's perfect AlB

The measure it's amazing.

 

The last question

If I want an average in the final?

 

 

But don't worry. Your measure is perfect

igorabdo's avatar
igorabdo
Advocate II
7 years ago

Hi AlB. Do you know what's happen?

  • AlB's avatar
    AlB
    Community Champion
    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]
    )
)

     

     

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    igorabdo

     

    The other option is that we take the average  with the filled-in values. Again, we are using the [Mesure_CUSTO] defined previously.

    Let me know if this solves the issue.

     

    Measure_CUSTO_With_Avg@TotalWithFilledValues2 = 
AVERAGEX (
    ADDCOLUMNS (
        SUMMARIZE ( Table1; Table1[ITEMID]; Table1[Mês Ano] );
        "Result"; [Measure_CUSTO]
    );
    [Result]
)
  • igorabdo's avatar
    igorabdo
    Advocate II
    7 years ago

    AlB
    Perfect!!!!

    The best solution.

     

     

    Thanks very much.

  • p_rathinavel's avatar
    p_rathinavel
    Advocate I
    4 years ago

    Hi AlB this is awesome !!

     

    it worked well as a measure but can you help me for a column in the same logic. thanks !!