cancel
Showing results for 
Search instead for 
Did you mean: 
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!

 

chart1.pngThis is what I have now

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

 

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
OwenAuger Community Champion
Community Champion

Re: Top 5 and others

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.

 



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

Proud to be a Datanaut!




View solution in original post

9 REPLIES 9
OwenAuger Community Champion
Community Champion

Re: Top 5 and others

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

 

 

 

 

 



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

Proud to be a Datanaut!




cecytabv
Frequent Visitor

Re: Top 5 and others

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

OwenAuger Community Champion
Community Champion

Re: Top 5 and others

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.

 



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

Proud to be a Datanaut!




View solution in original post

Highlighted
cecytabv
Frequent Visitor

Re: Top 5 and others

 

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

 

 

 

 

 

 

 

OwenAuger Community Champion
Community Champion

Re: Top 5 and others

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



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

Proud to be a Datanaut!




cecytabv
Frequent Visitor

Re: Top 5 and others

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

 

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

 

Thank you!!

charte mejorado.jpgThis is what I want to get.

 

 

 

 

OwenAuger Community Champion
Community Champion

Re: Top 5 and others

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 🙂



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

Proud to be a Datanaut!




cecytabv
Frequent Visitor

Re: Top 5 and others

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

 

 

beadysquared
Regular Visitor

Re: Top 5 and others

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?

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors