cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
olivierpkz Frequent Visitor
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) :

 

NameGenderSalary
Employee 1Male1000
Employee 2Male2000
Employee 3Female3000
Employee 4Male4000
Employee 5Male5000
Employee 6Male6000
Employee 7Male7000
Employee 8Male8000
Employee 9Male9000
Employee 10Male10000
Employee 11Female11000
Employee 12Male12000
Employee 13Male13000
Employee 14Male14000
Employee 15Female15000

 

So the TOP 10 salary will be :

 

TOP 10 Salary  
Employee 15Female15000
Employee 14Male14000
Employee 13Male13000
Employee 12Male12000
Employee 11Female11000
Employee 10Male10000
Employee 9Male9000
Employee 8Male8000
Employee 7Male7000
Employee 6Male6000

 

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

Female2
Male8

 

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 Smiley Happy

 

Thank you !

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Minimum value by category in a topN table ?

Hi @olivierpkz ,

 

To create a measure as below.

 

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

Capture.PNG

 

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
Super User

Re: Minimum value by category in a topN table ?

@olivierpkz 

 

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
Super User

Re: Minimum value by category in a topN table ?

Hi @olivierpkz 

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
Community Support Team

Re: Minimum value by category in a topN table ?

Hi @olivierpkz ,

 

To create a measure as below.

 

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

Capture.PNG

 

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.
olivierpkz Frequent Visitor
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 !

olivierpkz Frequent Visitor
Frequent Visitor

Re: Minimum value by category in a topN table ?

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

Helpful resources

Announcements
Virtual Launch Event

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.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

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

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 217 members 2,166 guests
Please welcome our newest community members: