Calculate Average correctly and Rank a column using a calculated measure

[Datagulf]

Name	KPI1(%)	KPI2(%)	KPI3(%)	KPI4(%)	KPI5(%)	Average	Rank
A	100	80		90	96	0.7
b	88	70	90	93	88	0.85
c	91	94	97			0.56
d			98	94	87	0.56
e	89		90	89	100	0.74

I have a column name and Measures KPI 1 to KPI 5 all in percentage form. I have two problems here. 
First I want to calculate the average of the KPI's ignoring the blanks so if Name A has 4 KPIs filled, I want to divide by 4 for the average. This goes for all the names. That means I will need a count that does not have the blanks. 

The second thing I want to rank the averages from the highest to the Lowest and be able to return the names of the top 3 in some cards. Every answer will be highly appreciated. 

[Datagulf]

Hey @selimovd it worked. I appreciate.

[selimovd · Accepted solution]

Hey @Datagulf ,

you can get the correct average with the following measure:

Average KPI = 
VAR vTableKPIs =
    UNION(
        ROW( "KPIs", SUM( myTable[KPI1(%)] ) ),
        ROW( "KPIs", SUM( myTable[KPI2(%)] ) ),
        ROW( "KPIs", SUM( myTable[KPI3(%)] ) ),
        ROW( "KPIs", SUM( myTable[KPI4(%)] ) ),
        ROW( "KPIs", SUM( myTable[KPI5(%)] ) )
    )
RETURN
    AVERAGEX(
        vTableKPIs,
        [KPIs]
    )

For the ranking you should get your result with the RANKX function.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Datagulf]

Hey @selimovd , Thank you so much, this almost solves it. However, some of the KPI's are Measures derived from different tables. Like a division, how do we go about specifying the table name?