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
        )

)

      

AnthonyGenovese's avatar
AnthonyGenovese
Resolver III
3 years ago

First, put what you are returning in a var result.
Then to debug, output your variables, pct_LTvsAT & MaxLTvsAT within that measure. Are those returning the values you expect?

If this post was helpful, please kudos or accept the answer as a solution.
~ Anthony Genovese
Need more PBI help? PM me for affordable, dedicated training or consultant recomendations!

  • RegionH's avatar
    RegionH
    Helper I
    3 years ago

    Thank you for the advice. I found out that the variable "MaxLTvsAT" is not returnering the expected value 4,30. It's returnering the same result as 'pct_LTvsAT'. So I have to figure out what's wrong with the calculation of variable "MaxLTvsAT". I also changed the sorting from ASC to DESC. I can see the total value in the bottom of the table is correct, when I return the variable result of "MaxLTvsAT", but the individual values per supplier is not what I expect.


    • AnthonyGenovese's avatar
      AnthonyGenovese
      Resolver III
      3 years ago

      If the total is showing the correct value for that variable, then all you need to do is remove the filters applied at the row level. For example, if you are looking at things by transaction id, do a ALL(TransactionID)

      It will look somthing like  

      var MaxLTvsAT = CALCULATE(maxx(TOPN(1, MaxLTvsAT_table, [@_pct_LTvsAT],ASC),[@_pct_LTvsAT]), ALL(TransactionID))

       If this post was helpful, please kudos or accept the answer as a solution.
      ~ Anthony Genovese
      Need more PBI help? PM me for affordable, dedicated training or consultant recomendations!