Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Hspuybroek
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
v-yulgu-msft
Employee
Employee

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!
Blog
Twitter
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!
Blog
Twitter
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.