Geomean Help

[villa1980]

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

[bhanu_gautam · Accepted solution]

@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 · Accepted solution]

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.

[bhanu_gautam · Accepted solution]

@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]

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

[villa1980]

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