Ranking based on different groupings

Hi pbifantic ,

 

I am not sure if I understood your requirement. You mentioned that "Likewise, need to count the company when the first faulty part was sent.  In this case Company 1 need to be classified into Feb, not Jan.", however, I cannot see "Yes" in the Faulty part column in February for Company 1. 

 

With this caveat in mind, to achieve the desired results, you can use DAX formulas in Power BI to rank and filter data based on shipment dates and faulty part shipments. For counting companies based on their first shipment, you can create a calculated column to rank the shipments for each company by date. This can be done using the RANKX function, which will assign a rank to each shipment within the same company, ordered by the shipment date in ascending order.

For example, the DAX formula for ranking shipments might look like this:

Rank_Shipment = 
RANKX(
    FILTER(
        'Table',
        'Table'[Company ID] = EARLIER('Table'[Company ID])
    ),
    'Table'[Shipped date],
    , ASC
)

Once you have this rank, you can create a measure to count only the companies whose rank is 1, ensuring you are counting only the first shipment for each company. The measure might look like this:

Count_First_Shipment = 
CALCULATE(
    DISTINCTCOUNT('Table'[Company ID]),
    FILTER(
        'Table',
        'Table'[Rank_Shipment] = 1
    )
)

For counting companies based on their first faulty part shipment, a similar approach can be taken. You can create a calculated column to rank only the shipments where the Faulty part column is marked as "Yes". The DAX formula for this ranking would be:

Rank_Faulty = 
IF(
    'Table'[Faulty part] = "Yes",
    RANKX(
        FILTER(
            'Table',
            'Table'[Company ID] = EARLIER('Table'[Company ID]) && 'Table'[Faulty part] = "Yes"
        ),
        'Table'[Shipped date],
        , ASC
    )
)

Once the rank for faulty shipments is determined, you can create a measure to count companies based on their first faulty shipment. The measure can be written as:

Count_First_Faulty = 
CALCULATE(
    DISTINCTCOUNT('Table'[Company ID]),
    FILTER(
        'Table',
        'Table'[Rank_Faulty] = 1
    )
)

In both cases, you can use these measures in your visualizations to display the counts grouped by the Shipped date column aggregated by months. This ensures that each company is counted only once based on either their first shipment or their first faulty shipment. If you need to filter your data table to show only the first shipment or faulty part for each company, you can use a calculated column to mark rows where the rank equals 1 and apply this column as a filter. This approach ensures accurate and clear representation of the data as per the requirements.

 

I have attached an example pbix file. 

 

 

Best regards,