Forum Discussion

cecytabv's avatar
cecytabv
Frequent Visitor
9 years ago
Solved

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 yo...
Interesting Links
Need Help
  • OwenAuger's avatar
    OwenAuger
    9 years ago

    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.

     

OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
9 years ago

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 :)

cecytabv's avatar
cecytabv
Frequent Visitor
9 years ago

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.