Forum Discussion

LoryMenCR's avatar
LoryMenCR
Helper I
1 year ago

Measure total won't work

Dear All,

I have the following measure:

 

ValoreDistribuito100 =
var total100 = CALCULATE([Saldo_ANALITICA_ TOTALI CORRETTI], 'db'[CEN_CODICE] = "100")
var currentcode = MAX('db'[CEN_CODICE])
var allocationpercent =
LOOKUPVALUE(
    'CEN_Weight%'[%],
    'CEN_Weight%'[CEN_END], currentcode,
    'CEN_Weight%'[CEN_START], "100"
)

RETURN
total100 * allocationpercent / 100
 
 
Measure is working properly but it returns an incorrect total (it always returns the value of the last row):

 

Do you know how can I fix this?

Thanks a lot

lor

Need Help

5 Replies

  • SolomonovAnton's avatar
    SolomonovAnton
    Super User
    1 year ago

    The issue with your measure returning an incorrect total is likely due to context evaluation. In Power BI, row-level calculations can sometimes lead to unexpected totals because the total row doesn’t have the same row context as individual rows. Instead, you need to adjust the measure to calculate totals differently from individual row calculations.

    You can fix this by wrapping your calculation in an IF statement that checks if it’s being evaluated in a row context or a total context. The HASONEVALUE function is useful here, as it returns TRUE if only one value is in the context (i.e., a row context), and FALSE for the total context. Here’s how to modify your measure:

    ValoreDistribuito100 =
    VAR total100 = CALCULATE([Saldo_ANALITICA_ TOTALI CORRETTI], 'db'[CEN_CODICE] = "100")
    VAR allocationpercent =
    SUMX(
    'CEN_Weight%',
    IF(
    'CEN_Weight%'[CEN_START] = "100" && 'CEN_Weight%'[CEN_END] = MAX('db'[CEN_CODICE]),
    'CEN_Weight%'[%],
    0
    )
    )

    RETURN
    IF(
    HASONEVALUE('db'[CEN_CODICE]),
    total100 * allocationpercent / 100,
    SUMX(
    ALL('db'[CEN_CODICE]),
    total100 * allocationpercent / 100
    )
    )



    • LoryMenCR's avatar
      LoryMenCR
      Helper I
      1 year ago

      Dear SolomonovAnton , thank you for your super fast answer.

      I fear I cannot use the HASONEVALUE here.

      My 'CEN_Weight%' table is as follows: 

       

      Do you think I can use some other solution?

       

       

       

      • SolomonovAnton's avatar
        SolomonovAnton
        Super User
        1 year ago

        if you need create calculation column tru to use this code:
        ValoreDistribuito100_Column =
        VAR total100 = CALCULATE([Saldo_ANALITICA_ TOTALI CORRETTI], 'db'[CEN_CODICE] = "100")
        VAR currentcode = 'db'[CEN_CODICE]
        VAR allocationpercent =
        LOOKUPVALUE(
        'CEN_Weight%'[%],
        'CEN_Weight%'[CEN_END], currentcode,
        'CEN_Weight%'[CEN_START], "100"
        )
        RETURN
        IF(NOT(ISBLANK(allocationpercent)), total100 * allocationpercent / 100, BLANK())

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi LoryMenCR ,

    Based on the description, try using the following DAX formula.

    ValoreDistribuito100 =
SUMX(
    'db',
    var total100 = CALCULATE([Saldo_ANALITICA_ TOTALI CORRETTI], 'db'[CEN_CODICE] = "100")
    var currentcode = 'db'[CEN_CODICE]
    var allocationpercent =
    LOOKUPVALUE(
        'CEN_Weight%'[%],
        'CEN_Weight%'[CEN_END], currentcode,
        'CEN_Weight%'[CEN_START], "100"
    )
    RETURN
    total100 * allocationpercent / 100
)

    Besides, I want to know if having relationship between two tables. Is this [Saldo_ANALITICA_ TOTALI CORRETTI] a column or a metric? Can you provide the sample data?

     

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.