cancel
Showing results for
Did you mean:
Frequent Visitor

## Minimum value by category in a topN table ?

Hello !

I have a DAX formula problem to retrieve the minimum value by category based on a TOPN filtered table.

Here is the context :

In a payroll database I must calculate how many man / woman are in the TOP10 salary.

Here are the simplified tables :

Table_salary (in the real table I have 1 row by employee / month) :

 Name Gender Salary Employee 1 Male 1000 Employee 2 Male 2000 Employee 3 Female 3000 Employee 4 Male 4000 Employee 5 Male 5000 Employee 6 Male 6000 Employee 7 Male 7000 Employee 8 Male 8000 Employee 9 Male 9000 Employee 10 Male 10000 Employee 11 Female 11000 Employee 12 Male 12000 Employee 13 Male 13000 Employee 14 Male 14000 Employee 15 Female 15000

So the TOP 10 salary will be :

 TOP 10 Salary Employee 15 Female 15000 Employee 14 Male 14000 Employee 13 Male 13000 Employee 12 Male 12000 Employee 11 Female 11000 Employee 10 Male 10000 Employee 9 Male 9000 Employee 8 Male 8000 Employee 7 Male 7000 Employee 6 Male 6000

And the result with the number of man/woman in this TOP 10 :

 Female 2 Male 8

In this example I need to obtain "2" and "Female"

I easily done it in Power Bi Desktop using visual with TOPN filter on it then grouping by gender, BUT I need to calculate this in a DAX measure and this is when my problems begin...

After several hours of research on the forum, my best (non working) formula is this :

MINX(SUMMARIZE(TOPN(10;[Table_Salary];[Salary];DESC);'Employee'[Gender]);[Number of Employees])

I try to summarize the TOP10 "Table_Salary" by gender, then calculate the minimum count of employees, but the result is false.

If someone can help me on this problem, it will be awesome

Thank you !

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Minimum value by category in a topN table ?

Hi @olivierpkz ,

To create a measure as below.

```Measure 2 =
VAR k =
Employee,
"Rank_", RANKX ( ALL ( Employee ), Employee[Salary],, DESC )
)
VAR b =
FILTER ( k, [Rank_] <= 10 )
RETURN
CALCULATE ( COUNTROWS ( Employee ), KEEPFILTERS ( b ) )
```

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Super User

## Re: Minimum value by category in a topN table ?

Try this measure

```Measure =
VAR DesiredRow =
TOPN (
1,
SUMMARIZE (
TOPN ( 10, Table_Salary, [Salary], DESC ),
[Gender],
"Count", COUNTROWS ( Table_Salary )
),
[Count], ASC
)
RETURN
MINX ( DesiredRow, [Gender] ) & UNICHAR ( 10 )
& MINX ( DesiredRow, [Count] )
```
Highlighted
Super User

## Re: Minimum value by category in a topN table ?

Try this:

1. Place Table1[Gender] in the rows of a matrix visual

2. Create this measure and place in in the matrix visual:

`Measure =SUMX (ADDCOLUMNS ( Table5; "Rank_"; RANKX ( ALL ( Table5 ); Table5[Salary];; DESC ) );IF ( [Rank_] <= 10; 1; 0 ))`

Community Support Team

## Re: Minimum value by category in a topN table ?

Hi @olivierpkz ,

To create a measure as below.

```Measure 2 =
VAR k =
Employee,
"Rank_", RANKX ( ALL ( Employee ), Employee[Salary],, DESC )
)
VAR b =
FILTER ( k, [Rank_] <= 10 )
RETURN
CALCULATE ( COUNTROWS ( Employee ), KEEPFILTERS ( b ) )
```

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Minimum value by category in a topN table ?

Hello and thank you for your help !

Your measure is great but the final result must be only the lower number of employees by gender, in your example it will  be "2".

So I think we must add a summarize by gender, then a minimum to extract the lower value.

Can you please show me how to do it, I try to do it myself but I don't know how to mix your final COUNTROWS to obtain this.

Thank you !

Frequent Visitor

## Re: Minimum value by category in a topN table ?

@v-frfei-msft any ideas how to do it ? Thank you very much !

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### PBI Community Highlights

Check out what's new in the Power BI Community!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 217 members 2,166 guests
Recent signins: