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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
robertosangi
Helper I
Helper I

Highest value per cluster

Hi, 

 

In my case I want to obtain the highest value (top 1) of a column divided per cluster. Here the example:

robertosangi_0-1646208711449.png


Actually I filtered my column as per picture because is not possible to filter per TOP N:

robertosangi_1-1646208754087.png

 

As you can se from the image "Palermo Ovest" in this case has only one row and it is ok but "Partinico" has different values because of many cases greater than 10. 

How can I show only the highest per each cluster in bold.

Thanks 

1 ACCEPTED SOLUTION

Hi @robertosangi ,

 

According to your screenshot, I think your table should look like as below.

1.png

I think you want to show values in red box.

1.png

I suggest you to create a rank measure and use it as a filter in visual level filter.

RANK =
VAR _RANK =
    RANKX (
        ALLEXCEPT ( 'Table', 'Table'[Unite], 'Table'[Blue Team] ),
        CALCULATE ( SUM ( 'Table'[IGB_NoLoc] ) ),
        ,
        DESC,
        DENSE
    )
RETURN
    _RANK

Result is as below.

1.png

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
amitchandak
Super User
Super User

@robertosangi , Create a rank on the innermost column and filter on 10

 

rankx(allselected(Table[cluster]), [IGB],,desc,dense)

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Hi @amitchandak , i don't want to filter on 10 but on the maximum value. It is possible?

Hi @robertosangi ,

 

Do you want to filter your matrix visual by two filters, 1. values in IGB_Noloc are greater than 10, 2. maxest value in  IGB_Noloc in per cluster ?

Here I suggest you to create a measure to filter your visual.

My sample:

1.png

Measure:

IGB_NoLoc Greater than 10 and Top 1 =
VAR _IGB_NoLoc =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( 'Table', 'Table'[Type] = "IGB_NoLoc" )
    )
VAR _MAX_IGB_NoLoc =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Unite] = MAX ( 'Table'[Unite] )
                && 'Table'[Level2] = MAX ( 'Table'[Level2] )
                && 'Table'[Type] = "IGB_NoLoc"
                && 'Table'[Value] > 10
        )
    )
RETURN
    IF ( _IGB_NoLoc = _MAX_IGB_NoLoc, 1, 0 )

Add this measure into visual level filter of matrix and set it show items when value =1.

Result is as below.

2.png

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-rzhou-msft thanks for your reply. I want to filter only per the highest value if possibile. Not filtering per 10 value

Hi @robertosangi ,

 

I think my measure will show you the highest value in IGB_Noloc. If this still couldn't help you solve your problem, please show me a screenshot with the result you want, this will make me easier to find the solution.

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-rzhou-msft,

Sure, I'll try to explain better

robertosangi_0-1646644355587.png


As you can see this is the AS IS situation. I want, per each red category, the highest IGB_NoLoc value. So, in this case for PALERMO EST only the first and the third value. In PALERMO OVEST only the first.

It is clear? I'll try to explain better if not.

 

Thanks

Hi @robertosangi ,

 

According to your screenshot, I think your table should look like as below.

1.png

I think you want to show values in red box.

1.png

I suggest you to create a rank measure and use it as a filter in visual level filter.

RANK =
VAR _RANK =
    RANKX (
        ALLEXCEPT ( 'Table', 'Table'[Unite], 'Table'[Blue Team] ),
        CALCULATE ( SUM ( 'Table'[IGB_NoLoc] ) ),
        ,
        DESC,
        DENSE
    )
RETURN
    _RANK

Result is as below.

1.png

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

October NL Carousel

Fabric Community Update - October 2024

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