ranking

Hi Hemant_Jaiswar ,

Lets go step by step:

1. Create a Calendar Table
You start by ensuring that you have a calendar table. If you don't already have one, you can create one with the DAX formula CALENDARAUTO(), which automatically generates a date range based on the data in your model.

Calendar = CALENDARAUTO()

2. Create a Month & Year Column
In the calendar table, you create a calculated column that combines the month and year in the format "Mmm-YYYY". This will be useful for grouping data by month and year.

Month & Year = FORMAT('Calendar'[Date],"Mmm-Yyyy")

3. Create a Sorter Column
Next, create a "Sorter" column to ensure that the "Month & Year" column is sorted correctly (chronologically). You can achieve this by creating a column that formats the date as "YYYYMM", which ensures the proper order.

Sorter = FORMAT('Calendar'[Date],"YyyyM")

Make sure that the sorter column data type is Whole Number:

Now your calendar table should look like this:

4. Sort Month & Year Column
After creating the "Sorter" column, select the "Month & Year" column in your calendar table and sort it by the "Sorter" column to ensure that months are sorted correctly.

5. Create Relationship Between Sales and Calendar Table
You need to create a relationship between the "Sales" table and the "Calendar" table. Use the "convertedDate" from the "Sales" table and the "Date" from the "Calendar" table. Make sure that the relationship has a cardinality of "Many to One" from "Sales" to "Calendar" (many sales to one date).

6. Create a Numeric Range Parameter
Next, create a parameter (usually a slicer) that lets users specify how many top items to show. This is commonly used for filtering to show a specific number of items, like the top N. Create the parameter with the name TopNParameter and ensure the "Add slicer to this page" checkbox is selected.

7. Create Last6Months Measure
This measure marks months within the last six months (excluding the current month) as "1" and all other months as "0". It uses EOMONTH to get the start and end of the range for the last six months.

Last6Months = 
VAR CurrentDate = TODAY()
VAR StartDate = EOMONTH(CurrentDate, -7) + 1
VAR EndDate = EOMONTH(CurrentDate, -1)
RETURN
    IF(
        MAX('Calendar'[Date]) >= StartDate &&
        MAX('Calendar'[Date]) <= EndDate,
        1,
        0
    )

8. Create Sales by Rank Measure
Now, create a measure to calculate sales for the top N items based on a ranking of sales. This measure uses RANKX to rank "LineName" based on sales (in descending order), then filters by the rank, and limits the results based on the TopN parameter.

Sales by Rank = 
VAR vRank =
    RANKX(
        ALLSELECTED(Sales[LineName]), -- Remove o contexto de datas
        CALCULATE(SUM(Sales[DomesticNOS]), REMOVEFILTERS('Calendar')), -- Calcula o total sem filtro de datas
        ,
        DESC,
        DENSE
    )
VAR TotalVisible =
    CALCULATE(
        SUM(Sales[DomesticNOS]),
        FILTER(
            ALLSELECTED(Sales[LineName]),
            RANKX(
                ALLSELECTED(Sales[LineName]),
                CALCULATE(SUM(Sales[DomesticNOS]), REMOVEFILTERS('Calendar')),
                ,
                DESC,
                DENSE
            ) <= topNParameter[TopNParameter Value]
        )
    )
RETURN
    IF(
        ISINSCOPE(Sales[LineName]),
        IF(vRank <= topNParameter[TopNParameter Value], SUM(Sales[DomesticNOS]), BLANK()),
        TotalVisible
    )

9. Create Matrix Visual
Now, create a matrix visual in Power BI. In the "Rows" field, drag the "Month & Year" column from the "Calendar" table. In the "Columns" field, drag the "LineName" column from the "Sales" table. In the "Values" field, drag the Sales by Rank measure.

10. Apply Filter to the Matrix Visual
With the matrix visual selected, go to the "Filters" pane. Drag the Last6Months measure into the "Filters on this visual" section, and set the filter to only show items where the value is "1".

11. Result
At this point, your matrix visual should display only the sales for the top N items from the last six months, excluding the current month.

Download the project here: Powerbi.pbix 

Hemant_Jaiswar 

Create a calculated column to identify the last 6 months excluding the current month:

Last6MonthsFlag = 
VAR CurrentMonth = EOMONTH(TODAY(), -1)
RETURN
IF(
'Table'[ConvertedDate] >= EOMONTH(CurrentMonth, -6) + 1 
&& 'Table'[ConvertedDate] <= CurrentMonth,
1,
0
)

Apply this column as a filter in the report to focus only on the last 6 months of data.

Create a Measure for Total Sales

TotalSales = SUM('Table'[Sum of DomesticNOS])

Rank LineNames based on their total sales in the last 6 months:

LineNameRank = 
VAR TotalSalesForPeriod = 
CALCULATE(
[TotalSales],
'Table'[Last6MonthsFlag] = 1
)
RETURN
RANKX(
ALL('Table'[LineName]),
TotalSalesForPeriod,
,
ASC/DESC, // Use DESC for Top N, ASC for Bottom N
Dense
)

Create a measure to filter the Top N or Bottom N LineNames:

IsInTopN = 
VAR SelectedN = SELECTEDVALUE('SlicerTable'[TopN])
RETURN
IF(
[LineNameRank] <= SelectedN,
1,
0
)

Create a parameter table (SlicerTable) for Top N selection with values like 3, 5, 10, etc., and bind it to the IsInTopN measure.

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn