Normalizing grouped data for multi-variate ranking

[AlanRGroskreutz]

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.

[Anonymous · Accepted solution]

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

[AlanRGroskreutz]

Thanks @Anonymous , that did the trick, but for a couple of changes. 

The first ADDCOLUMNS needed to use ALL(Rutas) so that the result could be shown per route on a table.  Also there needed to be a comma before "@USNormalized", but with those two additions it did what I wanted it to.

I have another measure that will rank these results, so it doesn't matter if it's o-3 or 0-1.

[AlB]

Hi @AlanRGroskreutz 

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.