Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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"
)
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 |
Does each of these two examples represent a different date or what exactly?
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
45 | |
28 | |
21 | |
11 | |
8 |
User | Count |
---|---|
77 | |
50 | |
46 | |
16 | |
12 |