Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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] )
Solved! Go to 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
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.
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] )
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
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()
)
@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))
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |