Forum Discussion

ADSL's avatar
ADSL
Icon for Post Prodigy rankPost Prodigy
2 years ago
Solved

Showing Last 03 Month Average Sales Rolling

Hi BI Community Team,   As per purpose, we want to see and track only top 10 customers only that average purchased at least 03 month with high amount. Then I have search and found this dax: Top10...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi ADSL 

     

    Please try this:

    Here I create a Measure:

    MEASURE =
VAR _Slicer_Month =
    MONTH ( MAX ( 'Calendar'[Date] ) )
VAR _Slicer_Year =
    YEAR ( MAX ( 'Calendar'[Date] ) )
VAR __Month =
    SUMMARIZE (
        SELECTCOLUMNS (
            CALENDAR (
                DATE ( _Slicer_Year, _Slicer_Month - 3, 1 ),
                DATE ( _Slicer_Year, _Slicer_Month - 1, 1 )
            ),
            "_Month", FORMAT ( [Date], "YYYY-mm" )
        ),
        [_Month]
    )
VAR _vtable =
    SUMMARIZE (
        ALL ( 'Sales Order' ),
        Customers[Customer Name],
        'Sales Order'[Month],
        "_SUMSales", SUM ( 'Sales Order'[Sales_Amt] )
    )
VAR _vtable2 =
    SUMMARIZE (
        _vtable,
        'Customers'[Customer Name],
        "_AVG",
            VAR _currentName =
                SELECTEDVALUE ( Customers[Customer Name] )
            RETURN
                AVERAGEX (
                    FILTER ( _vtable, [Month] IN __Month && [Customer Name] = _currentName ),
                    [_SUMSales]
                )
    )
RETURN
    IF (
        SELECTEDVALUE ( Customers[Customer Name] )
            IN SELECTCOLUMNS ( TOPN ( 10, _vtable2, [_AVG], DESC ), [Customer Name] ),
        SUM ( 'Sales Order'[Sales_Amt] )
    )

    Add it to the table visual, the result is as follow:

     

     

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ADSL's avatar
ADSL
Icon for Post Prodigy rankPost Prodigy
2 years ago

Hi dlopesc04 ,

 

Thank you for your feedback. As per my expectation,

1. customer name will show only top 10 that calculate from average sales from last 03 month

 

2. Total Sales value will show the month that select of slicer.

 

Any suggestion/advice?

dlopesc04's avatar
dlopesc04
Icon for Resolver II rankResolver II
2 years ago

ADSL 

Any suggestion to show the Top10 customers with last 03 month average sales rolling?

What you wanted has already been achieved in the pbix I've sent.

2. Total Sales value will show the month that select of slicer.

Maybe I didn't understand fully, but if you want to show nothing on total rows you can do this:

Top102_rolling = 

var ReferenceDate = MAX(Calendar[Date])
var PreviousDate = DATESINPERIOD(Calendar[Date], ReferenceDate, -3, MONTH)


var Result = 
CALCULATE([Total Sales],
REMOVEFILTERS('Calendar'),
KEEPFILTERS(PreviousDate),
KEEPFILTERS((TOPN(10, ALLSELECTED(Customers[Customer Name]), [Total Sales], DESC))))/CALCULATE(DISTINCTCOUNT('Sales Order'[Month]),REMOVEFILTERS('Calendar'),KEEPFILTERS(PreviousDate))

RETURN
IF(ISINSCOPE(Customers[Customer Name]),Result,BLANK())



  • ADSL's avatar
    ADSL
    Icon for Post Prodigy rankPost Prodigy
    2 years ago

    Thank you for your feedback.