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
cecytabv
Frequent Visitor

Top 5 and others

Hi everyone!

 

I am interested in knowing how can I create a Top 5 + Others in a database. The others value's sum has to appear in line number 6. Therefore, the chart only has to have 6 lines as you can see in the second picture. Please, help me!

 

This is what I have nowThis is what I have now

 Here you can find the picture of what I need to do===>. Please, help me!

 

THis is what I want to do, but I don't know how to achieve it.THis is what I want to do, but I don't know how to achieve it.

 

 

1 ACCEPTED SOLUTION

Hi @cecytabv

 

The basic change you need to make is to modify the filter context in which the TOPN function is called to be just the latest month.

 

This way, you will see values for all months, but the TOPN is determined only in the latest month.

 

The measures should be modified to something like:

 

Sales Amount Top =
CALCULATE (
    [Sales Amount],
    KEEPFILTERS (
        CALCULATETABLE (
            TOPN ( [TopN Selection], ALL ( Sales[Customer] ), [Sales Amount] ),
            LASTNONBLANK ( Calendar[Month], 0 )
            // Note: Assumes Calendar[Month] has a natural sort order
            // Could possibly use LASTDATE for a date column
        )
    )
)

and similarly for the Other measure.

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

9 REPLIES 9
OwenAuger
Super User
Super User

Hi @cecytabv

 

Have a look at my post here:

http://community.powerbi.com/t5/Desktop/Top-10-Other/m-p/52120#M20971

(There is a link to a pbix there)

 

It is pretty much the layout you're looking for - may need slight changes to control when totals are displayed.Untitled.png

 

 

 

 

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Thank you Dear @OwenAuger, it worked very very nice, but I have another issue to be solved. I am drawing a monthly evolution chart, so I need to see the last month's top 5 evolution. In conclusion, I have to visualize the orange highlighted institutions' evolution.

 

Do you have any solution? Thank you.

 

 

 

 

 

 rank por mejorar.jpg

Hi @cecytabv

 

The basic change you need to make is to modify the filter context in which the TOPN function is called to be just the latest month.

 

This way, you will see values for all months, but the TOPN is determined only in the latest month.

 

The measures should be modified to something like:

 

Sales Amount Top =
CALCULATE (
    [Sales Amount],
    KEEPFILTERS (
        CALCULATETABLE (
            TOPN ( [TopN Selection], ALL ( Sales[Customer] ), [Sales Amount] ),
            LASTNONBLANK ( Calendar[Month], 0 )
            // Note: Assumes Calendar[Month] has a natural sort order
            // Could possibly use LASTDATE for a date column
        )
    )
)

and similarly for the Other measure.

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

How do I change this code so that the TOPN is computed for across all dates in the visual instead of only for the last month?

 

Dear @OwenAuger could you help me with the complete solution, please?

 

I do not know how to make the Sales amount others measure. Also, what else do I have to do to solve this problem?

Could you try to solve it completely?

 

We are very near to the solution. Thank you.

 

This is what I am trying to get.

 

 charte mejorado.jpg

 

 

 

 

 

 

 

Hi @cecytabv

Sure, can help. Could you please post a link to a working pbix file (sanitised data if necessary)?

 

That would make it easier to build the measures and ensure correct table/column names.

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Dear @OwenAuger, attached you will find my pbix file wich is incomplete yet.

 

https://1drv.ms/u/s!AoBYecoXQCY6iCZl_QPtD3kXVqMT

 

Thank you!!

This is what I want to get.This is what I want to get.

 

 

 

 

Hi again @cecytabv

 

I have constructed some formluas that do what you want. It was actually a little more complicated than I expected, and I have used variables to make it clearer.

 

In both of these measures:

  1. LastMonthWithData is computed as the last vMes[IdFecha] value which has corresponding values in Data_PanelClienteExterna table.
  2. TopInLastMonth is a table containing the TopN values of Data_PanelClienteExterna[CodigoInstitucionOriginal] in LastMonthWithData.
  3. The measure is then evaluated in the context of this filter (or the complement).

Have a play with that and see if it's doing what you expect.

 

Dropbox link

 

Sales Amount Top = 
VAR LastMonthWithData =
    CALCULATETABLE (
        CALCULATETABLE ( LASTDATE ( vMes[IdFecha] ), Data_PanelClienteExterna ),
        ALLSELECTED ( vMes )
    )
VAR TopInLastMonth =
    CALCULATETABLE (
        TOPN (
            [TopN Selección],
            ALL ( Data_PanelClienteExterna[CodigoInstitucionOriginal] ),
            [Sales Amount]
        ),
        LastMonthWithData,
        ALL ( vMes )
    )
RETURN
    CALCULATE ( [Sales Amount], KEEPFILTERS ( TopInLastMonth ) )

 

Sales Amount Other = 
VAR LastMonthWithData =
    CALCULATETABLE (
        CALCULATETABLE ( LASTDATE ( vMes[IdFecha] ), Data_PanelClienteExterna ),
        ALLSELECTED ( vMes )
    )
VAR TopInLastMonth =
    CALCULATETABLE (
        TOPN (
            [TopN Selección],
            ALL ( Data_PanelClienteExterna[CodigoInstitucionOriginal] ),
            [Sales Amount]
        ),
        LastMonthWithData,
        ALL ( vMes )
    )
RETURN
CALCULATE (
    [Sales Amount],
    KEEPFILTERS (
        EXCEPT (
            ALL ( Data_PanelClienteExterna[CodigoInstitucionOriginal] ),
            TopInLastMonth
        )
    )
)

Regards,

Owen 🙂


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Hi again @OwenAuger. It worked very well, but I cannot draw it. It happens because I have Top and Others in another column. Therefore, It does not work with bar chart. Do you have any idea about how can I concatenate them?

 

This is what I need. Thanks for your help.

barchrt.jpg

 

 

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.