Blank values in matrix (usage TopN slicer)

[KevinN]

I have a table named 'IT Help Desk' with a column 'TicketID'. There is also a column named 'ITOwnerID' for the people who solves the 'TicketID's'. Those IT owner's can manage multiple tickets. As last there's a column with TicketType. I'm using a matrix with as Rows the ITOwnerID's and the Columns the TicketType for example 'Issue' and 'Request'.

I also have a table, see it as a dimension for the ITOwners but there's also a extra row named "Others". I work with a TopN and "Others" so when I select top 5 in my slicer I have the count of tickets for my top 5 but I also want the sum of the tickets for my "Others". In the code below you can see the total of tickets for the "Others" group. The problem is that when I use this measure in my matrix in the 'Values' field and i change my slicer from top 3 to top 5, sometimes I have blank values in my 'TicketType' when there's supposed to be values.

Table 'IT Help Desk'

Table 'Owners'

Relationship

TopNWithOthers = IF(
    RANKX(ALL('Owners'[ITOwnerID]); CALCULATE(COUNT('IT Help Desk'[TicketID]))) <= SELECTEDVALUE('TopN'[TopN]);
    CALCULATE(COUNT('IT Help Desk'[TicketID]));
    IF(
        HASONEVALUE(Owners[ITOwnerID]);
        IF(
            VALUES(Owners[ITOwnerID]) = "Others";
            SUMX(FILTER(ALL(Owners[ITOwnerID]); RANKX(ALL('Owners'[ITOwnerID]); CALCULATE(COUNT('IT Help Desk'[TicketID]))) > SELECTEDVALUE('TopN'[TopN])); CALCULATE(COUNT('IT Help Desk'[TicketID])))
        )
    )
)

Example 1 (Top 4)

Example 2 (Top 6)

[v-cherch-msft]

Hi @KevinN

It seems you may check the relationships. Sample data and expected output will be helpful to provide an accurate solution. You can upload the pbix file to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

Regards,

Cherie

[KevinN]

Hello,

Thanks for the reply and yes, I also think it's better to provide the Power BI file.

Is it good for you if I share the file via We Transfer, here is the link to it: https://we.tl/t-WihQ5HHWMX

It's sample data, so not real data that I use to boost my skills.

I guess the problem has to do with the measure I use named 'TopNWithOthers' or the relationship. I don't know.

Kind regards

[v-cherch-msft · Accepted solution]

Hi @KevinN

It seems the issue with the rank measure as below picture. In matrix visual the rank values are not less than the selected value. Please check if below rank measure could help you.

Rank =
CALCULATE (
    RANKX (
        ALL ( 'Owners'[ITOwnerID] ),
        CALCULATE ( COUNT ( 'IT Help Desk'[TicketID] ) )
    ),
    ALL ( 'IT Help Desk'[TicketType] )
)

Regards,

Cherie

[KevinN]

Thank you very much for this answer. It works perfectly.

It was just that little line of code that was missing where I take all the tickettypes into account

Kind regards