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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
olivierpkz
Regular 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 🙂

 

Thank you !

1 ACCEPTED SOLUTION

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 others find it more quickly.

View solution in original post

5 REPLIES 5
Zubair_Muhammad
Community Champion
Community Champion

@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] )

Regards
Zubair

Please try my custom visuals

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 others find it more quickly.

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 !

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

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 )
)

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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