Top 2 each month with new deals every months

[Anonymus_18]

I need to create a booked column.Where Top 2 will be booked in 2 types of region 2 in california and 2 in others. So once the deal id is booked it should not be considered for next subsequemt months.

month  dealid  Zone  billed/not  cost  Booked Nov-23  77234  California  Billed  10000  Yes Nov-23  34351  nEwyork  Billed  9999  Yes Nov-23  12653  New england  Billed  8765   Nov-23  54321  California  Billed  5435  Yes Nov-23  45637  Texas  Not Billed  7800   Nov-23  56789  New england  Billed  9980  Yes 23-Dec  77234  California  Billed  9000   23-Dec  34351  nEwyork  Billed  9999   23-Dec  12653  New england  Billed  9999  Yes 23-Dec  54329  California  Billed  5035  Yes 23-Dec  567482  texas  Billed  12000  Yes 23-Dec  987635  New york  Billed  13000  

[HarshSathwara19]

Steps:
1. Sort the Data by Month and Cost
Ensure your data is sorted by Month (ascending) and then by Cost (descending) within each month. This ensures that the most expensive deals are considered first.
2. Create a "Booked" Column with DAX
Use a DAX calculated column to determine whether a deal is "Booked" for each month.

Logic:

Partition the data by Zone (California or Other).
For each month, pick the top 2 deals in California and top 2 in Other Zones.
Mark these as "Booked".
Exclude previously booked deals from subsequent months.
3. DAX Implementation
First, create a calculated column for the "Region Type":

DAX
Copy code
RegionType =
IF(
'Table'[Zone] = "California",
"California",
"Other"
)
Next, create the "Booked" column:

DAX
Copy code
Booked =
VAR CurrentMonth = 'Table'[Month]
VAR CurrentRegion = 'Table'[RegionType]
VAR CurrentDealID = 'Table'[DealID]

VAR TopDeals =
FILTER(
'Table',
'Table'[Month] = CurrentMonth &&
'Table'[RegionType] = CurrentRegion &&
'Table'[Billed/Not] = "Billed" &&
NOT 'Table'[DealID] IN
CALCULATETABLE(
VALUES('Table'[DealID]),
'Table'[Booked] = "Yes",
'Table'[Month] < CurrentMonth
)
)

VAR RankWithinRegion =
RANKX(
TopDeals,
'Table'[Cost],
,
DESC,
DENSE
)

RETURN
IF(
RankWithinRegion <= 2,
"Yes",
"No"
)

[HarshSathwara19]

1. Sort the Data by Month and Cost

• Ensure your data is sorted by Month (ascending) and then by Cost (descending) within each month. This ensures that the most expensive deals are considered first.

  2. Create a "Booked" Column with DAX

  Use a DAX calculated column to determine whether a deal is "Booked" for each month.

  Logic:

  • Partition the data by Zone (California or Other).
  • For each month, pick the top 2 deals in California and top 2 in Other Zones.
  • Mark these as "Booked".
  • Exclude previously booked deals from subsequent months.

    ---

    3. DAX Implementation

    First, create a calculated column for the "Region Type":

    RegionType =
    IF(
    'Table'[Zone] = "California",
    "California",
    "Other"
    )

    Next, create the "Booked" column:

    Booked =
    VAR CurrentMonth = 'Table'[Month]
    VAR CurrentRegion = 'Table'[RegionType]
    VAR CurrentDealID = 'Table'[DealID]

    VAR TopDeals =
    FILTER(
    'Table',
    'Table'[Month] = CurrentMonth &&
    'Table'[RegionType] = CurrentRegion &&
    'Table'[Billed/Not] = "Billed" &&
    NOT 'Table'[DealID] IN
    CALCULATETABLE(
    VALUES('Table'[DealID]),
    'Table'[Booked] = "Yes",
    'Table'[Month] < CurrentMonth
    )
    )

    VAR RankWithinRegion =
    RANKX(
    TopDeals,
    'Table'[Cost],
    ,
    DESC,
    DENSE
    )

    RETURN
    IF(
    RankWithinRegion <= 2,
    "Yes",
    "No"
    )

    Best Regards,
    HSathwara.

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

     

[Kedar_Pande]

@Anonymus_18 

Create a "Rank" Column for Each Region

RankInRegion = 
RANKX(
FILTER(
'Table', 
'Table'[Zone] = EARLIER('Table'[Zone]) && 'Table'[Month] = EARLIER('Table'[Month]) && 'Table'[Billed/not] = "Billed"
), 
'Table'[Cost], 
, 
DESC, 
Dense
)

Create a "Booked" Column Based on Top 2 Deals

Booked = 
IF(
'Table'[Billed/not] = "Billed" && 
(
('Table'[Zone] = "California" && 'Table'[RankInRegion] <= 2) || 
('Table'[Zone] <> "California" && 'Table'[RankInRegion] <= 2)
), 
"Yes", 
"No"
)

Exclude Already Booked Deals from Future Months

Booked = 
IF(
'Table'[Billed/not] = "Billed" && 
(
('Table'[Zone] = "California" && 'Table'[RankInRegion] <= 2) || 
('Table'[Zone] <> "California" && 'Table'[RankInRegion] <= 2)
) && 
ISBLANK(
CALCULATE(
MAX('Table'[Booked]),
'Table'[DealID] = EARLIER('Table'[DealID]),
'Table'[Month] < EARLIER('Table'[Month])
)
), 
"Yes", 
"No"
)

💡 If this helped, please give Kudos 👍 or mark it as a Solution ✅.
Best regards,
Kedar
🌐 Connect on LinkedIn

[Anonymus_18]

@Kedar_Pande @It's not working I'm still getting deals which are already in top 2 in previous month again in the following month

[Anonymus_18]

Once thedeal.is booked it shouldn't be considerer for futire months eventhough it's cost is higher or it's in top 10.that deal should be excluded and and the 11 th rank deal should be included