Measure total won't work

[LoryMenCR]

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

[v-jiewu-msft]

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.

[LoryMenCR]

Not working 😞

[SolomonovAnton]

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]

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]

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())