TopN and all other on graph chart, with other slicers on.

[leopoldvili_1]

Hi, I would like to visualize topN and all others on a graph chart.  I have the following table: 

and I am using a formula to calculate ViolationsNTop and other:

ViolationsNTop = var Rankviolationbycount =RANKX(ALL('Table'),'Table'[Counts],,Desc)

return

IF(Rankviolationbycount<=4,'Table'[Error],"Other")

 

Howewer, It works partly, but when I select filter ID: it does not work as expected, it should show the top 4 violations+others for selected filter AA1. 

Can anyone help here?

Link to Pbx: https://we.tl/t-llcVQEIZgr

Thank you.

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[leopoldvili_1]

Wow, Thank you very much! It works. Only I would love to have "others"as the last values in the graph? is it easily possible? 

[Ashish_Mathur · Accepted solution]

You are welcome.  PBI file attached.

Hope this helps.

[leopoldvili_1]

Thank you very much! it is exactly, what I wanted! Wow, thank you one more time.

[Ashish_Mathur]

You are welcome.

[Anonymous]

Thank you @lbendlin  for your prompt response.

Hi @leopoldvili_1 ,
Based on the information you have provided, you may consider following the steps below to resolve your issue:
1. Add two new columns.

AA1 = 
VAR _RANK =
    RANKX (
        FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) ),
        'Table'[Counts],
        ,
        DESC
    )
RETURN
    IF (
        _RANK <= 4
            && 'Table'[ID] = "AA1",
        'Table'[Error],
        IF ( 'Table'[ID] = "AA2", BLANK (), "Other" )
    )

AA2 = 
VAR _RANK =
    RANKX (
        FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) ),
        'Table'[Counts],
        ,
        DESC
    )
RETURN
    IF (
        _RANK <= 4
            && 'Table'[ID] = "AA2",
        'Table'[Error],
        IF ( 'Table'[ID] = "AA1", BLANK (), "Other" )
    )

2. Create a new table with three options.

3. Create a field parameter and establish a relationship with the new table, add Parameter in Y-axis.

4. Add a MEASURE to the visual corresponding to the options for filtering, add Table2 AA in slicer.

SELECTED = 
IF (
    SELECTEDVALUE ( Table2[AA] ) = "AA1"
        && SELECTEDVALUE ( 'Table'[AA1] ) <> BLANK (),
    1,
    IF (
        SELECTEDVALUE ( Table2[AA] ) = "AA2"
            && SELECTEDVALUE ( 'Table'[AA2] ) <> BLANK (),
        1,
        IF ( SELECTEDVALUE ( Table2[AA] ) = "ViolationsNTop", 1, 0 )
    )
)

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

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

[leopoldvili_1]

Thank you very much, for your so detailed explanation. It works if ID we have only 2, but in the real project I  might have several hundreds different IDs. I will try to find based on your provided Dax  solution, to change logic slighly. Thank you very much one more time.

[lbendlin]

Change ALL() to ALLSELECTED()

[leopoldvili_1]

Hi, thank you for you reply, Howewer, even after implementing " AllSelected", and when I select filter "AA1", it is not showing  grouped errors based on Id. [Example of output in the screenshot}