Forum Discussion

villa1980's avatar
villa1980
Resolver II
1 year ago
Solved

Geomean Help

Hi, me again.

I have 3 measures in a table, Align_veh, Exhaust_Veh, Brake_Veh and in a seperate table which are %. These return the values by Depot, example below...

                 Align_Veh     Exhaust_Veh       Brake_Veh

Depot 1    2.6%                  1.2%                 11.2%
Depot 2    14.5%                6.5%                  4.9%
Depot 3     0.1%                10.0%                 1.7%

 

The customer has asked that instead of an average to rank on for the depots they want to show the Geomean, but I have no idea how to set thisup in DAX or if there is a better way?

Thanks


Alex

Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    villa1980 , I have not used it ever but I got them in Microsoft documentation 

     

    https://learn.microsoft.com/en-us/dax/geomean-function-dax

     

    and it looks like you need to create a measure for each of your percentage columns (Align_Veh, Exhaust_Veh, Brake_Veh).

    DAX
    Align_Veh_Geomean = GEOMEAN('YourTable'[Align_Veh])
    Exhaust_Veh_Geomean = GEOMEAN('YourTable'[Exhaust_Veh])
    Brake_Veh_Geomean = GEOMEAN('YourTable'[Brake_Veh])

     

    If you need a single measure to rank the depots, you can combine these geometric means into one measure. For example, you can take the geometric mean of the three individual geometric means.

    DAX
    Overall_Geomean = GEOMEANX(
    { [Align_Veh_Geomean], [Exhaust_Veh_Geomean], [Brake_Veh_Geomean] },
    [Value]
    )

     

    Finally, you can create a ranking measure based on the overall geometric mean.

    Depot_Rank = RANKX(
    ALL('YourTable'[Depot]),
    [Overall_Geomean],
    ,
    DESC
    )

  • Anonymous's avatar
    Anonymous
    1 year ago

    Thanks for the reply from bhanu_gautam , please allow me to provide another insight:
    Hi  villa1980 ,

     

    You can try the following dax:

    Column=
VAR _Align_Veh =
    DIVIDE ( SUMX ( FILTER ( 'Tablename', [Ignore columns of 0] <> 0 ), [Align_Veh] ), 100 )
VAR _Brake_Veh =
    DIVIDE ( SUMX ( FILTER ( ' Tablename', [Ignore columns of 0] <> 0 ), [Brake_Veh] ), 100 )
VAR _Diag_Veh =
    DIVIDE ( SUMX ( FILTER ( ' Tablename', [Ignore columns of 0] <> 0 ), [Diag_Veh] ), 100 )
VAR _Exhaust_Veh =
    DIVIDE ( SUMX ( FILTER ( ' Tablename', [Ignore columns of 0] <> 0 ), [Exhaust_Veh] ), 100 )
RETURN
    ( _Align_Veh * _Brake_Veh * _Diag_Veh * _Exhaust_Veh ) ^ ( 1 / 11 ) * 100

    If the results do not meet your desired outcome, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    villa1980 , I have not used it ever but I got them in Microsoft documentation 

     

    https://learn.microsoft.com/en-us/dax/geomean-function-dax

     

    and it looks like you need to create a measure for each of your percentage columns (Align_Veh, Exhaust_Veh, Brake_Veh).

    DAX
    Align_Veh_Geomean = GEOMEAN('YourTable'[Align_Veh])
    Exhaust_Veh_Geomean = GEOMEAN('YourTable'[Exhaust_Veh])
    Brake_Veh_Geomean = GEOMEAN('YourTable'[Brake_Veh])

     

    If you need a single measure to rank the depots, you can combine these geometric means into one measure. For example, you can take the geometric mean of the three individual geometric means.

    DAX
    Overall_Geomean = GEOMEANX(
    { [Align_Veh_Geomean], [Exhaust_Veh_Geomean], [Brake_Veh_Geomean] },
    [Value]
    )

     

    Finally, you can create a ranking measure based on the overall geometric mean.

    Depot_Rank = RANKX(
    ALL('YourTable'[Depot]),
    [Overall_Geomean],
    ,
    DESC
    )

    • villa1980's avatar
      villa1980
      Resolver II
      1 year ago

      Thank-you for the reply, howevere, it does not seem to work on measures, it needs columns for it to work

      • villa1980's avatar
        villa1980
        Resolver II
        1 year ago

        So I have managed to work it out using a different formula, but now as there are 0 appearing in the data for other depots I am getting a 0% for geomean, how do I negate this 0 so I return the correct figure??
        This is my calculation

        Geomean = (([Align_Veh]/100)*([Brake_Veh]/100)*([Diag_Veh]/100)*([Exhaust_Veh]/100))^(1/11)*100
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks for the reply from bhanu_gautam , please allow me to provide another insight:
    Hi  villa1980 ,

     

    You can try the following dax:

    Column=
VAR _Align_Veh =
    DIVIDE ( SUMX ( FILTER ( 'Tablename', [Ignore columns of 0] <> 0 ), [Align_Veh] ), 100 )
VAR _Brake_Veh =
    DIVIDE ( SUMX ( FILTER ( ' Tablename', [Ignore columns of 0] <> 0 ), [Brake_Veh] ), 100 )
VAR _Diag_Veh =
    DIVIDE ( SUMX ( FILTER ( ' Tablename', [Ignore columns of 0] <> 0 ), [Diag_Veh] ), 100 )
VAR _Exhaust_Veh =
    DIVIDE ( SUMX ( FILTER ( ' Tablename', [Ignore columns of 0] <> 0 ), [Exhaust_Veh] ), 100 )
RETURN
    ( _Align_Veh * _Brake_Veh * _Diag_Veh * _Exhaust_Veh ) ^ ( 1 / 11 ) * 100

    If the results do not meet your desired outcome, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.