Create calculated column which shows the number 1 of a certain category

[Evelien]

Hi,

I would like to create a calculated column that shows the "company" which ranked number 1 for a specific month and product.

The last column in the table is what I am trying to achieve:

Thanks for your help.

Kind regards,

Evelien

[Zubair_Muhammad · Accepted solution]

@Evelien

[Zubair_Muhammad]

Hi @Evelien

Try this Calculated Column

Result=
CALCULATE (
    VALUES ( TableName[Company] ),
    FILTER (
        ALL ( TableName ),
        MONTH ( TableName[Month] ) = MONTH ( EARLIER ( TableName[Month] ) )
            && TableName[Product] = EARLIER ( TableName[Product] )
            && TableName[rank] = 1
    )
)

[Zubair_Muhammad · Accepted solution]

@Evelien

[Evelien]

Hi @Zubair_Muhammad,

I have applied this formula to a bigger dataset, and it does not work anymore.  First I changed the VALUES by FIRSTNONBLANK as I received an error that there were multiple values.

For most of the products the formula gives the nr 1 company, but for some products it gives the company which is ranked lower, like rank 2.

Do you know what I might be missing here?

Thanks a lot.

Kind regards,

Evelien

[Zubair_Muhammad]

Hi @Evelien

Please could you share your file via onedrive or googledrive?

I will try to solve it

[Evelien]

Hi @Zubair_Muhammad,

Many thanks for your help.

However the data is confidential and it is a big data set.

Just an idea, but could it be that the issue is caused by the fact that if there is no value for a product and company, there is no row of data?

Evelien

[Zubair_Muhammad]

Hi @Evelien

I think it could be because there are multiple companies for the same product that share RANK 1

Are there such cases?

If this is the case I have a workaround

[Zubair_Muhammad]

@Evelien

For example if for Product A both BLA and HELLO have same values say 300 both will be ranked 1.

And my formula could not determine which company to return and it throws that error  ...."Multiple values supplies"

What I can do for you in that case is to RETURN both companies separated by a Delimiter

BLA & HELLO

BLA | HELLO

BLA , HELLO

[Evelien]

Hi @Zubair_Muhammad,

I also thought that could be the issue, but it turns out that the values are unique.  A company which has a lower value than another company within the same month and product, still pops up as the number 1.  So this company has rank 2 for instance; for some reason it doesn't apply the part in the formula that indicates "rank = 1".

Kind regards,

Evelien

[Zubair_Muhammad · Accepted solution]

Hi @Evelien

Are there more than 1 year in your data? Then you need to add the parameter in RED below

Result =
CALCULATE (
    VALUES ( TableName[Company] ),
    FILTER (
        ALL ( TableName ),
        YEAR ( TableName[Month] ) = YEAR ( EARLIER ( TableName[Month] ) )
            && MONTH ( TableName[Month] ) = MONTH ( EARLIER ( TableName[Month] ) )
            && TableName[Product] = EARLIER ( TableName[Product] )
            && TableName[rank] = 1
    )
)

[Evelien]

Thanks, @Zubair_Muhammad 

[Zubair_Muhammad]

Hi @Evelien

Did it resolve the problem?

[Evelien]

Yes, it did 🙂

Sorry, I was so excited that I forgot to mention it 😄

[Evelien]

Fantastic @Zubair_Muhammad

Thanks a lot!!

Evelien