Forum Discussion
AlanRGroskreutz
5 years agoHelper II
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 late...
- Anonymous5 years ago
Efficiency Score2 = VAR FlightCounts = ADDCOLUMNS( DISTINCT( Rutas[ruta] ), "@FlightCount", [FlightCount], "@US", [Total US], "@PV", [PV-Orto] ) -- normalize per route VAR MinFlightCount = MINX( FlightCounts, [@FlightCount] ) VAR MaxFlightCount = MAXX( FlightCounts, [@FlightCount] ) VAR MinUS = MINX( FlightCounts, [@US] ) VAR MaxUS = MAXX( FlightCounts, [@US] ) var MinPV = MINX( FlightCounts, [@PV] ) var MaxPV = MAXX( FlightCounts, [@PV] ) VAR FlightCountsNormalized = ADDCOLUMNS( FlightCounts, "@FlightCountNormalized", var Delta = MaxFlightCount - MinFlightCount return DIVIDE( [@FlightCount] - MinFlightCount, Delta ) "@USNormalized", var Delta = MaxUS - MinUS return DIVIDE( [@US] - MinUS, Delta ), "@PVNormalized", var Delta = MaxPV - MinPV return DIVIDE( [@PV] - MinPV, Delta ) ) var Result = SUMX( FlightCountsNormalized, // I think this should be a weighted // average, not a sum but up to you. // If you don't average this, the // range will be from 0 to 3, instead of // from 0 to 1. [@FlightCountNormalized] + [@USNormalized] + [@PVNormalized] ) RETURN Result
AlB
5 years agoCommunity Champion
I don't understand this part:
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
Where exactly do you have that problem?
|
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |