Dax formula comparing one row to each other rows per group

[cris007]

Hello Community, 

I have the data below:

Brand	Client	Type	Value
AAA	BTC	My brand	5000
BBB	BTC	competitor	7000
CCC	BTC	competitor	4000
AAA	BTC	My brand	500
BBB	BTC	competitor	8000
CCC	BTC	competitor	800
AAA	B&B	My brand	8000
BBB	B&B	competitor	900
CCC	B&B	competitor	10000

I am looking for a DAX formula that would say : Per client, IF the value of "My brand"  is bigger than at least one of the competitor's value , than return "--"; IF the value of "My brand"  is bigger than the value of each competitors, than return "ok" ; IF the value of "My brand"  is smaller than the value of each competitors, than return "nok".

For example, for the client B&B, I will compare the value of the brand AAA (which is my brand) separetly to the value of BBB and CCC. My formula should return "--" since the value of AAA is bigger than at least one of the competitor for the client B&B. 

Thanks for the help

[tamerj1 · Accepted solution]

@cris007 
Please refer to attached sample file with a preliminary solution. Please check the file and amended with correct data sample if needed and advise exactly how do you want display the results either a calculated column or a measure and the shape of the report.

Flag = 
VAR CurrentProductClientTable = 
    CALCULATETABLE ( 
        'Table',
        ALLEXCEPT ( 'Table', 'Table'[Product Name], 'Table'[Client] )
    )
VAR MyBrandValue =
    SUMX ( 
        FILTER ( 
            CurrentProductClientTable,
            'Table'[Type] = "My brand"
        ),
        'Table'[Value]
    )
VAR CompetotorTable = FILTER ( CurrentProductClientTable, 'Table'[Type] = "competitor" )
VAR MaxCompetitorValue = MAXX ( CompetotorTable, 'Table'[Value] )
VAR MinCompetitorValue = MINX ( CompetotorTable, 'Table'[Value] )
RETURN
    SWITCH ( 
        TRUE ( ),
        MyBrandValue > MaxCompetitorValue, "Ok",
        MyBrandValue > MinCompetitorValue, "--",
        "NOk"
    )

[cris007]

many thanks for your help @tamerj1 

[cris007]

Hi @tamerj1 , thanks for your reply.

Yes sure:

for the client BTC , the formula should return "--" since the value of my brand (AAA) is bigger than at least one competitor's value but not all of them. 

An exemple for the formula returning "ok" woul be as below:

Brand	Client	Type	Value
AAA	BTC	My brand	15.000
BBB	BTC	competitor	9000
CCC	BTC	competitor	8000

And an exemple for the formula returning "ok" would be as below:

Brand	Client	Type	Value
AAA	BTC	My brand	2000
BBB	BTC	competitor	3000
CCC	BTC	competitor	4000

[tamerj1]

@cris007 

Does each of these two examples represent a different date or what exactly?

[cris007]

@tamerj1 no it is for one single date. there is no calendar in this case. 

[tamerj1]

@cris007 

Apparently I failed to properly express my question. 
If not date what gropus each "My Brand" with its relevant "Competitors" then what is? What other column(s) do you have in the table?

[tamerj1]

Hi @cris007 

Can describe the same example but for BTC?