Forum Discussion

heathernicole's avatar
heathernicole
Icon for Continued Contributor rankContinued Contributor
8 years ago
Solved

TopN Customers Rolling 12 months

Ok - So here's what I'm trying to recreate in Power BI (currently an excel file):     Logically, I get what's occurring in this report - however it's escaping me as to HOW to actually pull thi...
Need Help
  • mattbrice's avatar
    mattbrice
    8 years ago

    This is an interesting problem so I gave it a try.  Let me know if it doesn't work for you as I haven't tested these as I don't really have a good sample data set to work off of.  You'll have to translate the column names I used for your model.

    Here is what I came up with for 4 measures assuming you have YearMonth on rows like you have in your result screenshots.

     

    Total Sales = SUM ( FactSales[SalesAmount] )

     Then for total sales last 12 Mo

     

    Total Sales Last 12MO =
VAR _last12MO =
    DATESBETWEEN (
        DimDate[Datekey],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( DimDate[Datekey] ) ) ),
        LASTDATE ( DimDate[Datekey] )
    )
RETURN
    CALCULATE ( [Total Sales], _last12MO )

    then for rolling top 20 sales last 12 months:

     

    Total Sales Last 12 MO Rolling Top20 =
VAR _last12MO =
    DATESBETWEEN (
        DimDate[Datekey],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( DimDate[Datekey] ) ) ),
        LASTDATE ( DimDate[Datekey] )
    )
VAR _Top20Last12MO =
    CALCULATE (
        SUMX (
            VALUES ( DimDate[YearMonth] ),
            CALCULATE (
                [Total Sales],
                TOPN ( 20, VALUES ( DimCustomer[CustomerKey] ), [Total Sales] )
            )
        ),
        _last12MO
    )
RETURN
    _Top20Last12MO

    and finally the percentage is easy:

     

    Percentage of Sales = DIVIDE ( [Total Sales Last 12 MO Rolling Top20], [Total Sales Last 12MO] )

    Let me know if this worked.

  • heathernicole's avatar
    heathernicole
    8 years ago

    mattbrice - Hey -  I think this is right. :) I've modified a few things - but you gave me the core idea. THANK YOU!
    I'm getting ready to meet with our Sales Manager to verify the numbers but from what I've checked so far it seems accurate.

     

    THANKS SO MUCH! I'll post the final after I confirm. 

     

  • heathernicole's avatar
    heathernicole
    8 years ago

    Full answer with mattbrice base formulas from a test dataset -  I'm also including the measures for the Count of Customers. I redid the customers count - using the base that matt created. So shout-out to you, mattbrice! :) THANKS!

     

    Count of Customers =
CALCULATE (
    DISTINCTCOUNT ( 'SALES DETAILS'[LinkToCustomerID] ),
    'SALES DETAILS'[SalesTxn Document Type] = "Invoice",
    'Calendar - Transaction Date'[Year] >= 2017
)
    Count of Customers Last 12MO =
VAR _last12MO =
    DATESBETWEEN (
        'Calendar - Transaction Date'[Transaction Date],
        NEXTDAY (
            SAMEPERIODLASTYEAR (
                LASTDATE ( 'Calendar - Transaction Date'[Transaction Date] )
            )
        ),
        LASTDATE ( 'Calendar - Transaction Date'[Transaction Date] )
    )
RETURN
    CALCULATE ( [Count of Customers], _last12MO )
    Total Outgoing Sales =
CALCULATE (
    SUM ( 'SALES DETAILS'[Sales $$] ),
    'Calendar - Transaction Date'[Year] >= 2017
)
    Total Sales Last 12MO =
VAR _last12MO =
    DATESBETWEEN (
        'Calendar - Transaction Date'[Transaction Date],
        NEXTDAY (
            SAMEPERIODLASTYEAR (
                LASTDATE ( 'Calendar - Transaction Date'[Transaction Date] )
            )
        ),
        LASTDATE ( 'Calendar - Transaction Date'[Transaction Date] )
    )
RETURN
    CALCULATE ( [Total Outgoing Sales], _last12MO )
    Total Sales Last 12 MO Rolling Top20 =
VAR _last12MO =
    DATESBETWEEN (
        'Calendar - Transaction Date'[Transaction Date],
        NEXTDAY (
            SAMEPERIODLASTYEAR (
                LASTDATE ( 'Calendar - Transaction Date'[Transaction Date] )
            )
        ),
        LASTDATE ( 'Calendar - Transaction Date'[Transaction Date] )
    )
VAR _Top20Last12MO =
    CALCULATE (
        SUMX (
            VALUES ( 'Calendar - Transaction Date'[Month] ),
            CALCULATE (
                [Total Outgoing Sales],
                TOPN (
                    20,
                    VALUES ( 'SALES DETAILS'[LinkToCustomerID] ),
                    [Total Outgoing Sales]
                )
            )
        ),
        _last12MO
    )
RETURN
    _Top20Last12MO

    Final Result:

     

heathernicole's avatar
heathernicole
Icon for Continued Contributor rankContinued Contributor
8 years ago

ADDED BONUS - the correct answer is below :) But I added Dynamic TOPN filtering and thought it might be helpful to someone else. :)  I modifed the method used here: by the PowerPivotPro guys (Rob Collie wrote this particular article) just a touch to work in the Power BI model. Great article and there's a part 1 and part 3 included with it if you're interested. Reid Havens referenced all three of them in a blog post here. 

 

To make this report filter dynamically by the TOPN:

 

First I created a Top N table (same as in the article):

You do this just by 'Enter Data' and manually create the table obviously you can go up as high as you want - for this report it was 20.

 

 

 

The created the following measure: 

SelectedTopNNumber = MAX('TOPN'[TopN]) 

 

Then I modified mattbrice measure to reference the newly created TopN measure: It's bold and underlined. This way the TOPN is dynamic according to whichever TOPN number is selected. 

Total Sales Last 12 MO Rolling Top20 = 
VAR _last12MO =
    DATESBETWEEN (
        'Calendar - Transaction Date'[Transaction Date],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Calendar - Transaction Date'[Transaction Date] ) ) ),
        LASTDATE ( 'Calendar - Transaction Date'[Transaction Date] )
    )
VAR _Top20Last12MO =
    CALCULATE (
        SUMX (
            VALUES ( 'Calendar - Transaction Date'[Month] ),
            CALCULATE (
                [Total Outgoing Sales],
                TOPN ( [SelectedTopNNumber], VALUES ( 'SALES DETAILS'[LinkToCustomerID] ), [Total Outgoing Sales] )
            )
        ),
        _last12MO
    )
RETURN
    _Top20Last12MO

 

I then created a chiclet slicer (imported it from the Visual Store). Don't forget to disable multi-select within the filter. 

  

 

Just like the article - it gives you the ability to dynamically filter this sample dataset by a TopN filter. 

Filtered by Top 20:

 

Filtered by Top 5