TOPN with Field Parameters
Problem Statement: Field parameter has been used to create Axis/Dimension slicer. We would like TOPN to work based on Axis. We have two columns Brand and Category with a measure, net. And the Field parameter is created on Brand and Category. Select any one of these and the net’s topn of that should be shown based on that.
Model: I am using the standard sales model, I and using for all my videos and blogs. Sales fact with a key measure [net], joined with dimensions: Item, Geography, Date, and Customer.
Solution:
Created a field parameter on Brand and category from the Item Dimension. Refer to this blog to learn how to create field parameters: https://amitchandak.medium.com/power-bi-field-parameters-a-quick-way-for-dynamic-visuals-fc4095ae9af...
This is the table code
Parameter = {
("Brand", NAMEOF('Item'[Brand]), 0),
("Category", NAMEOF('Item'[Category]), 1)
}
Created a selectedvalue for the Axis parameter. As a direct selectedvalue not supported on the Field Parameter column, used this code
Selected Parameter = maxx(filter(Parameter ,Parameter[Parameter Order] = SELECTEDVALUE(Parameter[Parameter Order])), Parameter[Parameter])
Created a TOPN measure like
TOP2 Quick = SWITCH([Selected Parameter], "Brand" , CALCULATE([Net], TOPN(2,ALLSELECTED('Item'[Brand]), [Net],DESC), values('Item'[Brand])) ,
CALCULATE([Net], TOPN(2,ALLSELECTED('Item'[Category]), [Net],DESC), values('Item'[Category]))
)
When you select Category
When you select Brand
You can find more details on the blog
The file is attached.
Find all my Medium blogs here
Click Here to access all my blogs and videos in a jiffy via an exclusive visual glossary using Power BI.
Please like, share, and comment on these. Your suggestions on improvement, challenges, and new topics will help me explore more.
You Can watch my Power BI Tutorial Series on My Channel, Subscribe, Like, and share
