Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
Ilias_pal
Regular Visitor

Top N & Bottom N at the same visual including hierarchies

Hello,

 

I have the below DAX Measure "FilterMeasure 3added as filter in the visual which dynamically returns me the top 6 & bottom 2 "HRCHY_LVL_7" based on another measure "vs. Comparison Net Sales".

FilterMeasure 3 =
VAR CurrentBrand =
    SELECTEDVALUE ( 'Org Hierarchy Levels'[HRCHY_LVL_7] )
VAR T1 =
    ADDCOLUMNS (
        ALLSELECTED ( 'Org Hierarchy Levels'[HRCHY_LVL_7] ),
        "@PYSales", [vs. Comparison Net Sales]
    )
VAR Top6 =
    TOPN ( 6, T1, [@PYSales] )
VAR Bottom2 =
    TOPN ( 2, T1, [@PYSales], ASC )
VAR T2 =
    SELECTCOLUMNS ( UNION ( Top6, Bottom2 ), "@Brand", [HRCHY_LVL_7] )
RETURN
    IF ( CurrentBrand IN T2, 1 )
 
Ilias_pal_0-1680271313251.png

 

The measure works fine, however I would like to give the option to end users to  drill down more on hierarchy but I would like to always them to see the top 6 & bottom 2 based on the level they are. So my visual should be having the below on Y axis 
Ilias_pal_1-1680271449068.png

 

Unfortunately, the measure is not working with multiple data on Y Axis or it doesn`t give me for all levels the top 6 & bottom 2.

 

Can you please propose an idea how to resolve this?

 

Thank you very much in advance.

 

0 REPLIES 0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors