topic Re: Count TOP N for each Legend items that are Displayed on Graph in DAX Commands and Tips

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

cmaloyb — Tue, 17 May 2022 14:48:53 GMT

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:

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.

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

tamerj1 — Tue, 17 May 2022 15:23:11 GMT

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

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

cmaloyb — Tue, 17 May 2022 15:39:55 GMT

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.

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

tamerj1 — Wed, 18 May 2022 06:44:47 GMT

Hi cmaloyb
Would you please explain in further details?

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

cmaloyb — Wed, 18 May 2022 13:42:34 GMT

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.

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.

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

tamerj1 — Wed, 18 May 2022 17:30:45 GMT

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

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

Icey — Mon, 23 May 2022 07:57:27 GMT

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.

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

cmaloyb — Mon, 23 May 2022 19:41:09 GMT

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