Forum Discussion

AlanRGroskreutz's avatar
AlanRGroskreutz
Helper II
5 years ago
Solved

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...
grouping
Help Requested
normalize
ranking
  • Anonymous's avatar
    Anonymous
    5 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

     

Anonymous's avatar
Anonymous
Not applicable
5 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

 

  • AlanRGroskreutz's avatar
    AlanRGroskreutz
    Helper II
    5 years ago

    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.