Top N measure showing blank values

[Anonymous]

Hi,

I am trying to show Top 20 customers based on their usage and below is the measure for it. The measure works fine and shows only Top 20 customers when I add Customer field and Usage measure to a matrix, but when I add month to the matrix it gives some BLANK records rows also. I am just trying to understand the behavior of this measure in both scenrios, any solution/explanation would be helpful.

Top20 Usage = IF (
RANKX (
ALL(Customer[Customer_Name]),
[Usage],
,
DESC
)
<= 20,
[Usage]
)

[Anonymous]

I need to see the labels in the front of the report and the code that creates the Total. That appears to be the potential issue at a glance. The rows at the bottom have numbers but do not total. Also, they should disappear if you use the page/visualization filter instead of the RANKX to show top 20. 

[Anonymous]

Hi @Anonymous,

Thanks for the response. The labels infront of the numbers are just customer names which can't be posted. In the below measure 

Usage = SUM('Fact_Usage'[usage]) and the total in the visual is coming from this measure.

Top20 Usage = IF (
RANKX (
ALL(Customer[Customer_Name]),
[Usage],
,
DESC
)
<= 20,
[Usage]
)

[lalthan · Accepted solution]

The output is the expected behaviour. The formula displays the value only if the customer appears in the top 20. So, when month is added to the filter, it checks the top 20 customers for each of the months and displays the usage. So, a customer may appear in top 20 for one months but is not in the top 20 for the gross usage (i.e. all months combined). Hence, you get blank usage because at a gross level, the customer is not in the top 20.

To display all figures you may want to use a combination of rankx, allselected and hasonevalue

[Anonymous]

Hi @lalthan

Thanks lalthan, I think I got what I need.

cheers

[Anonymous]

Hi @lalthan,

Sorry for re-opening this thread. I tried to restrict the matrix to show only top 20 customers (based on gross usage) by Customer_Name and Month with the below measure but I still see few customers with no gross usage (blanks).

Top 20 Usage = IF (HASONEVALUE(FACT_USAGE[CUSTOMER_NAME]),
        IF(RANKX (
            ALLSELECTED(FACT_USAGE[CUSTOMER_NAME]),
            [USAGE],
            ,
            DESC
        )
            <= 20,
        [USAGE]
    ),BLANK()
)

[lalthan]

@Anonymous, kindly try below query. I've not validated the same but I think it should work

MinRank = calculate(MINX(values(FACT_USAGE[Month]),RANKX (all(FACT_USAGE[CUSTOMER_NAME]),[Usage],,DESC)))

Top 20 Usage = IF (HASONEVALUE(FACT_USAGE[Month]),
        IF(RANKX (ALLSELECTED(FACT_USAGE[CUSTOMER_NAME]),[Usage],,DESC) <= 20,[Brkg]),
        calculate([Brkg],filter(values(FACT_USAGE[CUSTOMER_NAME]),[MinRank]<=20))
)