Forum Discussion
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 all suppliers. I would expect the value to the left of the column '_Normalize score LT vs AT'.
In the screenshot you can see the correct results in the column 'Normaliseret', but this calculation is made of a calculated table and 3 separate measures. So I just try to build these measures (calculations) in to one measure '_normalize score LT vs AT'.
I'll appreciate if someone could help me in the right direction?
_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 = maxx(TOPN(1, MaxLTvsAT_table, [@_pct_LTvsAT],ASC),[@_pct_LTvsAT])
return
if(
pct_LTvsAT<0,
100,
100-(divide(pct_LTvsAT,MaxLTvsAT,0)*100
)
)
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 ) )
4 Replies
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!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.
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 likevar 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!
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 ) )
