Dynamic Top 10 and Other Power BI desktop on Tabular model

[Hspuybroek]

Hi,

I already spoke with @OwenAuger and his solution from the topic http://community.powerbi.com/t5/Desktop/Top-10-Other/m-p/200596#M88246. Because that topic was closed, I opened this topic. I want to use it like his example: https://www.dropbox.com/s/59cct4in6zqbxaj/Sales%20Top%20Other.pbix?

But function like Except doesn't work with Power BI desktop if you use a Tabular model. Also variables are not possible. Otherwise this could also be a solution: http://sqljason.com/2017/03/dynamic-grouping-in-power-bi-using-dax.html

@OwenAuger came with a solution that I tried and this works:

Sales Amount Other Total Only =
IF ( NOT ( ISFILTERED ( Sales[Customer] ) ); [Sales Amount Other] )

I saw in your example that it has totals and every else is empty, but in my example everything is empty even the totals. I used your example: Sales Amount Other NO EXCEPT WITHOUT VARIABLES.

The formula works:

Uitval Top Overig = CALCULATE([Selected Top N Value];

KEEPFILTERS(
( FILTER (
ALL('ANG Fouten'[LPP_NAME]);
NOT (CONTAINS(
TOPN([Selected Top NNumber]; ALL('ANG Fouten'[LPP_NAME]);[Selected Top N Value]);'ANG Fouten'[LPP_NAME];'ANG Fouten'[LPP_NAME])
)
))))

But there is also a formula to get only the total of the other group:

Uitval Overig Totaal =
IF (( NOT( ISFILTERED ( 'ANG Fouten'[LPP_NAME] ) );[Uitval Top Overig]))

And that formula doesn't work / gives a empty result. @OwenAuger Can you help me with this?

Or has somebody else an other solution?

Regards, Hilbert

[v-yulgu-msft]

Hi @Hspuybroek,

In order to change the Expect function with some other possible methods, please replace the highlighted part with below formula:

Sales Amount Other = 
CALCULATE (
    [Sales Amount],
    KEEPFILTERS (  
            TOPN (COUNTROWS(Sales)- [TopN Selection], ALL ( Sales[Customer] ), [Sales Amount],ASC )
        
    )
)

Please check whether above advice works in your scenario.

Regards,
Yuliana Gu

[Hspuybroek]

Hi @v-yulgu-msft

Thank you, like the solution that i mentioned this will work. But I want only the total and that will not work combining with in my case the LPP name see screen above.

But after adding the LPP name I don't see my other category back. That is what i want.

To get the total of the other category i did the following:

Uitval Overig Totaal =
IF (( NOT( ISFILTERED ( 'ANG Fouten'[LPP_NAME] ) );[Sales Amount Other]))

And then later:

Uitval Top & Overig =
IF (
    HASONEVALUE ( 'Top en Overig'[Top_soort]);
    SWITCH (
        VALUES ( 'Top en Overig'[Top_soort] );
        "Top"; [Uitval Top];
        "Overig";[Uitval Overig Totaal]
    );
    [Selected Top N Value]
)

But the Catergory Overig is empty because I don't get any total in the Uitval Overig Totaal measure. Do you have any solution?

Thank you!

Regards, Hilbert

[Hspuybroek]

@OwenAuger

Do you have a solution?

[OwenAuger]

Hi @Hspuybroek

I haven't yet had a chance to re-look at this one - will read through this thread properly and get back to you.

Regards,

Owen

[Hspuybroek]

Hi @OwenAuger That would be great!

[OwenAuger]

@Hspuybroek

Sorry for delay in replying - have read your question and would be easier to answer with a sample model to figure out what is going on.

Do you have one you can upload?

[Hspuybroek]

@OwenAuger

I was on vacation, that is the reason for my late response. Here is the link to the dashboard: https://www.dropbox.com/s/oz3p97x2niv9caf/ANG%20Dashboard.pbix?dl=0

The folder formules contains the calculation that i have used. I hope that you can see something with a tabular connection as a source.

Regards, Hilbert