Forum Discussion

RegionH's avatar
RegionH
Helper I
3 years ago
Solved

Using variables to normalize a score is not working

Hi I'm trying to normalize a score by the following DAX formula, where I use a variable table and two variable values. But it is not working as you can see in the screenshot. I get the value 0 for a...
dax formula
Max value
normalize
table variable
  • RegionH's avatar
    RegionH
    3 years ago

    I found a solution by myself, which I think will work. The solution is written in this code and it is the last variable:

    _normalize score LT vs AT = 
var MaxLTvsAT_table = SUMMARIZE('data-leverandørperformance',[Leverandør ID], "@_pct_LTvsAT", '1. Measure Tabel'[% Lead time vs Aftalt tid])
var pct_LTvsAT = DIVIDE([Gns. LT vs AT],[Gns. Aftalt leveringstid],0)
var MaxLTvsAT = calculate(maxx(MaxLTvsAT_table,[@_pct_LTvsAT]),all('Leverandør'))
var TopSupplier = topn(1,MaxLTvsAT_table,[@_pct_LTvsAT],DESC) -- will extract top 1 row from virtual table _table
var max_value = SUMMARIZE(TopSupplier,[@_pct_LTvsAT])
var Maximum = CALCULATE([% Lead time vs Aftalt tid], topn(1,all('Leverandør'[Leverandør]),[% Lead time vs Aftalt tid],DESC)) -- This one worked
return

    if(
        pct_LTvsAT<0,
        100,
        100-(divide(pct_LTvsAT,Maximum,0)*100
        )

)

      

RegionH's avatar
RegionH
Helper I
3 years ago

I found a solution by myself, which I think will work. The solution is written in this code and it is the last variable:

_normalize score LT vs AT = 
var MaxLTvsAT_table = SUMMARIZE('data-leverandørperformance',[Leverandør ID], "@_pct_LTvsAT", '1. Measure Tabel'[% Lead time vs Aftalt tid])
var pct_LTvsAT = DIVIDE([Gns. LT vs AT],[Gns. Aftalt leveringstid],0)
var MaxLTvsAT = calculate(maxx(MaxLTvsAT_table,[@_pct_LTvsAT]),all('Leverandør'))
var TopSupplier = topn(1,MaxLTvsAT_table,[@_pct_LTvsAT],DESC) -- will extract top 1 row from virtual table _table
var max_value = SUMMARIZE(TopSupplier,[@_pct_LTvsAT])
var Maximum = CALCULATE([% Lead time vs Aftalt tid], topn(1,all('Leverandør'[Leverandør]),[% Lead time vs Aftalt tid],DESC)) -- This one worked
return

    if(
        pct_LTvsAT<0,
        100,
        100-(divide(pct_LTvsAT,Maximum,0)*100
        )

)