normalize

3 Topics

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 ) )
Solved
RegionH
2 years ago Place DAX Commands and Tips
1.2KViews
0likes
4Comments
Help with DAX for Scaled Score Matrix
Hi all, I've been trying to create a DAX measure to generate scaled score (wihout intermediate tables) for a competition but unsuccessful. This is how the end matrix is required in Power BI Scaled Score Team 1 Team 2 Team 3 Cooking 1 0 0.70 Driving 1 0.93 0 Reading 0 1 0.53 Singing 1 0 0 Sports 1 0.36 0 The forumula that is used to calcuate the scaled score: Scaled Score for an Activity = ((Sum of the respective Team's Activity Mins) - (Minimum of Activity Mins across teams)) / ((Maximum of Activity Mins across teams) - (Minimum of Activity Mins across teams)) For e.g., Scaled Score of Team 3 for Cooking = (150-20)/(205-20) = 0.703 This is the source table, named Activity: This table contains the list of activities (with duration in mins) perfomed by each candidate. Team Candidate Name Activity Activity Mins Team 1 Man 1 Singing 90 Team 3 Man 1 Singing 35 Team 2 Man 4 Driving 30 Team 3 Man 2 Reading 80 Team 3 Man 4 Cooking 30 Team 1 Man 2 Reading 65 Team 1 Man 5 Cooking 85 Team 3 Man 5 Cooking 15 Team 1 Man 1 Sports 85 Team 1 Man 4 Sports 75 Team 1 Man 5 Driving 30 Team 2 Man 4 Sports 65 Team 2 Man 3 Sports 25 Team 2 Man 3 Reading 20 Team 1 Man 4 Sports 35 Team 1 Man 3 Singing 75 Team 2 Man 4 Singing 35 Team 3 Man 2 Reading 35 Team 3 Man 4 Driving 30 Team 3 Man 3 Sports 30 Team 2 Man 1 Driving 70 Team 1 Man 4 Driving 75 Team 1 Man 5 Cooking 85 Team 1 Man 5 Cooking 35 Team 3 Man 5 Cooking 50 Team 2 Man 3 Cooking 20 Team 3 Man 5 Cooking 55 Team 2 Man 2 Reading 75 Team 2 Man 3 Reading 65 Team 1 Man 3 Singing 75 This is the measure I wrote after a lot of research, but it isn't working as intended Scaled Score = VAR MinValue = CALCULATE( MINX( SUMMARIZE(Activity, Activity[Team]), CALCULATE( SUM(Activity[Activity Mins]) ) ) ) VAR MaxValue = CALCULATE( MAXX( SUMMARIZE(Activity, Activity[Team]), CALCULATE( SUM(Activity[Activity Mins]) ) ) ) VAR TeamTotal = CALCULATE( SUM(Activity[Activity Mins]), ALLEXCEPT(Activity, Activity[Activity]) ) RETURN DIVIDE(TeamTotal - MinValue, MaxValue - MinValue, 0) Any help in getting the DAX measure is appreciated. Thanks in advance!
Solved
NKumar
3 years ago Place DAX Commands and Tips
863Views
0likes
2Comments
Normalizing grouped data for multi-variate ranking
Hi all, I'm having a problem normalizing grouped data in Dax. Here's the whole story. I am trying to creat an inefficiency ranking for some airline routes that would be able to be filtered later by airline, date, etc. Therefore, the flight data has to be grouped by the routes flown and those grouped values ranked. This worked fine as long as I just used the sum of three separate rankings, and then ranked that total. Now, however, the customer said (and I agree) that it would be more accurate to normalize the grouped data first, then make the three rankings, then the overall ranking. Here is what I have tried to do, but doesn't work. Efficiency Score2 = -- set up flight count per route table to be normalized VAR FlightRank = summarize (Rutas, rutas[ruta], "Flights", [FlightCount]) -- normalize flights per route VAR FlightMnX = MIN(FlightRank) VAR FlightMxX = MAX(FlightRank) Var FlightNorm = DIVIDE(FlightRank-FLightMnX , FlightMxX - FlightMnX) -- set up US Totals per route table to be normalized VAR USRank = summarize (Rutas, rutas[ruta], "US", [Total US]) -- Normalize US Total Per route VAR USMnX = MIN(USRank) VAR USMxX = MAX(USRank) Var USNorm = DIVIDE(USRank-FLightMnX , FlightMxX - FlightMnX) -- set up Diff per route table to be normalized VAR DIffRank = summarize (Rutas, rutas[ruta], "US", [PV-Orto]) -- Normalize Diff Per route VAR DifMnX = MIN(DIffRank) VAR DifMxX = MAX(DIffRank) Var DifNorm = DIVIDE(DIffRank-FLightMnX , FlightMxX - FlightMnX) RETURN SUMX ( SUMMARIZE ( Rutas, Rutas[Ruta], "Flight Rank", FlightNorm, "US Rank", USNorm, "Diff Rank", DifNorm ), [Flight Rank] + [US Rank] + [Diff Rank] ) The problem I seem to be having is that I don't know how to (or can't) call a specific column from a virtual table that has more than one column. I know that what I have supplied won't give the final ranking. That is in a seperate measure that calls this result as the vector to be ranked. Any help would be appreciated.
Solved
AlanRGroskreutz
5 years ago Place DAX Commands and Tips
957Views
0likes
3Comments