by Hello Everyone,
In this article we will discucss & interested to show about the top performing category metrics along with rest of the categories total values in the matrix
Lets get into the business case!
I want to see the top 5 performing categories based on the selections when I randomly made in the slicer, if nothing is selected it will automatically show top 5 categories and the rest of the categories to be shown as Others in the Matrix or table visual.
Here is the sample data,
from the above data, If I select cities then it will show Top 5 in the Matrix remaining all will be show as Others. Lets create one table (Top Table) in power bi itself and enter the values below.
Now Create one DAX measure to RANK the cities by Values in the Matrix hierarchy(Scope)
Scope City Ranking =
IF(
ISINSCOPE(‘Fact'[City]),
RANKX(
ALLSELECTED(‘Fact'[City]),
CALCULATE(SUM(‘Fact'[Value])),
,
DESC
),
BLANK()
)
Now lets create Top 5 performed cities matrix by using the above explicit measure in switch condition,
Scope Top 5 Check =
INT(SWITCH(SELECTEDVALUE(‘Top Table'[City]), “Top City”,
[Scope City Ranking] <= 5,
“Others”,[Scope City Ranking] > 5))
Lets look the data modeling for understanding,
As per the data modeling we are not connected the Top Table to Fact, So when we create hierarchy of Top Table city below Fact City it is necessary to put values metrics measure into the values section of the matrix.
Values = SUM(‘Fact'[Value])
created the explicit measure for Value column and pull into the matrix,
Thats it you can see the top 5 cities in the Top City hierarchy and the remaining goes under Others section, lets select the cities in the slicer,
You can see I selected the cities as randomly and it will automatically arrange Top 5 descending order based on the values column and other cities that is more than 5 are fall under the Others Section.
If we have lot of hierarchy in the matrix then we need to remove those field filters in the Scope City Ranking measure by using ALL condition in CALCULATE DAX.
Thank you, Appreciate the time you spent to this!
See you in the next use case, Bye!!!
