cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Dynamic Top 10 and Other Power BI desktop on Tabular model

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

 

 

7 REPLIES 7
Microsoft
Microsoft

Hi @Hspuybroek,

 

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

2.PNG

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Top and other.PNGOnly top.PNG

 

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

 

 

@OwenAuger

 

Do you have a solution?

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

 


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn

Hi @OwenAuger That would be great!

@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?


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn

@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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors