Count TOP N for each Legend items that are Displayed on Graph

[cmaloyb]

I am trying to create a card that will display the number of Top N items based on a category. Please consider the bar graph below.

I would like to display two Card visuals above the chart that gives a total of the Top 5 Equipment that has the most maintenance jobs. This is the table (partial) used to create this visual along with the filters:

Only partial data.Visual level filter. No Page or Report filters.

The desired result on each card would total up the number of maintenance jobs pertaining to each Equipment Responsibility. The Aerospace Engineer card would display 144 whereas the Electrical Engineer card would display 230. I would like for this to be dynamic as well if there were to have slicers on the report (Airplane type from a future data table). I created a card using the Count of Job Number field and filtering that visual for Top 5 Count of Job number and then by Equipment Responsibility, however it will total the top 5 Electrical Engineer job number's, for example. 

I have tried writing a DAX formula, but to be fairly honest, I'm having trouble understanding the TOPN function and am even wondering if I'm even going the right direction.

[tamerj1]

Hi @cmaloyb 
Please try

Top 5 Engineers =
SUMX (
    TOPN (
        5,
        ADDCOLUMNS (
            VALUES ( TableName[Equipment Responsibilty] ),
            "@Count", CALCULATE ( DISTINCTCOUNT ( TableName[Job Number] ) )
        ),
        [@Count]
    ),
    [@Count]
)

Top 5 Equipment =
SUMX (
    TOPN (
        5,
        ADDCOLUMNS (
            VALUES ( TableName[Equipment] ),
            "@Count", CALCULATE ( DISTINCTCOUNT ( TableName[Job Number] ) )
        ),
        [@Count]
    ),
    [@Count]
)

[cmaloyb]

The second DAX Function seems to give me the correct total, however when filtering Engineer Responsibility, it give the sum of the top 5 for that selected Engineer Responsibility instead of the 3 that are displayed on the visual. Below, I have provided an idea of what I would potentially like for my card visuals to read.NOTE: The circled Card visuals are just static numbers for the sake of example.

[tamerj1]

Hi @cmaloyb 
Would you please explain in further details?

[cmaloyb]

When using this code.....

Top 5 Equipment =
SUMX (
    TOPN (
        5,
        ADDCOLUMNS (
            VALUES ( TableName[Equipment] ),
            "@Count", CALCULATE ( DISTINCTCOUNT ( TableName[Job Number] ) )
        ),
        [@Count]
    ),
    [@Count]
)

I was able to get "Top 5 Equipment" total of 374.

However, when I use "Equipment Responsibility" visual level filter on the "Top 5 Equipment" card, it filters for the TOP5 of the "Equipment Responsibility" that is filtered for. 

For example, if I filter "Equipement Responsibility on the the card for "Aerospace Engineer", I get 202, which is the count of the top 5 Aerospace equipment related jobs.  

Incorrect.

I am looking to have this filter only provide the count of what is displayed on the top 5 visual. The card should be 144.

[Icey · Accepted solution]

Hi @cmaloyb ,

Based on my test, you may need to filter TableName[Equipment Responsibility] inside the measure expression.

Top 5 Equipment - Aerospace Engineer =
SUMX (
    FILTER (
        TOPN (
            5,
            ADDCOLUMNS (
                SUMMARIZE (
                    TableName,
                    TableName[Equipment],
                    TableName[Equipment Responsibility]
                ),
                "@Count", CALCULATE ( DISTINCTCOUNT ( TableName[Job Number] ) )
            ),
            [@Count]
        ),
        [Equipment Responsibility] = "Aerospace Engineer"
    ),
    [@Count]
)

Top 5 Equipment - Electrical Engineer =
SUMX (
    FILTER (
        TOPN (
            5,
            ADDCOLUMNS (
                SUMMARIZE (
                    TableName,
                    TableName[Equipment],
                    TableName[Equipment Responsibility]
                ),
                "@Count", CALCULATE ( DISTINCTCOUNT ( TableName[Job Number] ) )
            ),
            [@Count]
        ),
        [Equipment Responsibility] = "Electrical Engineer"
    ),
    [@Count]
)

Best Regards,

Icey

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

[cmaloyb]

Thank you. This worked. Turns out we needed to add the filter to the DAX equation.

[tamerj1]

@cmaloyb 

Not sure if I fully understand but you may try

Top 5 Equipment =
SUMX (
    TOPN (
        5,
        ADDCOLUMNS (
            SUMMARIZE (
                TableName,
                TableName[Equipment],
                TableName[Equipment Responsibility]
            ),
            "@Count", CALCULATE ( DISTINCTCOUNT ( TableName[Job Number] ) )
        ),
        [@Count]
    ),
    [@Count]
)