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.
as you see this is What I'd like to solve, today.
I'greatly appreciate your help.
Thanks
Solved! Go to Solution.
You can use ROUNDDOWN() Function to round down the value to 2 decimals.
In my chart, when a company is selected, the highest value product in this company will be displayed in Card. Rank value of each product in this company will be displayed in Multi-row card.
When a product is selected, the highest value product in this company will be displayed in Card. Rank value and value of this product in this company will be displayed in Multi-row card.
Please refer to following detailed steps:
RankInCompany1 = RANKX ( FILTER ( Table1, EARLIER ( Table1[Selling company] ) = Table1[Selling company] ), Table1[Value] )
Title2 = VAR RankString = SWITCH ( Table1[RankInCompany1], 1, "most expensive", 2, "2nd", 3, "3rd", Table1[RankInCompany1] & "th" ) RETURN ( Table1[Brand name] & " is the " & RankString & " product of " & Table1[Selling company] & ", with a price of " & ROUNDDOWN(Table1[Value],2) & "€" )
MostExpensiveCar = IF ( HASONEVALUE ( Table1[Selling company] ), LOOKUPVALUE ( Table1[Brand name], Table1[Value], CALCULATE ( MAX ( Table1[Value] ), ALLEXCEPT ( Table1, Table1[Selling company] ) ) ) & " is the highest value product in this company", "Please highlight a company or a product." )
You can use ROUNDDOWN() Function to round down the value to 2 decimals.
In my chart, when a company is selected, the highest value product in this company will be displayed in Card. Rank value of each product in this company will be displayed in Multi-row card.
When a product is selected, the highest value product in this company will be displayed in Card. Rank value and value of this product in this company will be displayed in Multi-row card.
Please refer to following detailed steps:
RankInCompany1 = RANKX ( FILTER ( Table1, EARLIER ( Table1[Selling company] ) = Table1[Selling company] ), Table1[Value] )
Title2 = VAR RankString = SWITCH ( Table1[RankInCompany1], 1, "most expensive", 2, "2nd", 3, "3rd", Table1[RankInCompany1] & "th" ) RETURN ( Table1[Brand name] & " is the " & RankString & " product of " & Table1[Selling company] & ", with a price of " & ROUNDDOWN(Table1[Value],2) & "€" )
MostExpensiveCar = IF ( HASONEVALUE ( Table1[Selling company] ), LOOKUPVALUE ( Table1[Brand name], Table1[Value], CALCULATE ( MAX ( Table1[Value] ), ALLEXCEPT ( Table1, Table1[Selling company] ) ) ) & " is the highest value product in this company", "Please highlight a company or a product." )
One more thing sir, If I'd like to see the rankings per year, all I gotta do is to filter it per year no ?
You are a genius, thanks
If you’d like to see the rankings per year, you need to create some other columns with following steps. I assume you have a Date column with format m/d/yyyy.
Year = YEAR ( Table1[Date] )
RankInCompanyPerYear = RANKX ( FILTER ( Table1, EARLIER ( Table1[Selling company] ) = Table1[Selling company] && EARLIER ( Table1[Year] ) = Table1[Year] ), Table1[Value] )
TitlePerYear = VAR RankString = SWITCH ( Table1[RankInCompanyPerYear], 1, "most expensive", 2, "2nd", 3, "3rd", Table1[RankInCompanyPerYear] & "th" ) RETURN ( Table1[Brand name] & " is the " & RankString & " product of " & Table1[Selling company] & ", with a price of " & ROUNDDOWN(Table1[Value],2) & "€" & " in " & Table1[Year])
MostExpensiveCarPerYear = IF ( HASONEVALUE ( Table1[Selling company] ), LOOKUPVALUE ( Table1[Brand name], Table1[Value], CALCULATE ( MAX ( Table1[Value] ), ALLEXCEPT ( Table1, Table1[Selling company], Table1[Year] ) ) ) & " is the highest value product in this company in " & VALUES(Table1[Year]), "Please highlight a company or a product." )
@Simon_Hou, If I would like the dynamic text to tell me for instence, BMW X6 is the highest value product besides [product type], in this [segment] in the year of 2015 This two additional thing.
How could I do this ?
Thanks a lot your support
If you’d like to show the highest value product in all companies in a specified year. You can create a new measure which is only a bit different from the measure of “MostExpensiveCarPerYear” as below.
MostExpensiveCarPerYear_AllCompany =
IF (
HASONEVALUE ( Table1[Selling company] ),
LOOKUPVALUE (
Table1[Brand name],
Table1[Value], CALCULATE (
MAX ( Table1[Value] ),
ALLEXCEPT ( Table1, Table1[Year] )
)
)
& " is the highest value product within all companies in " & VALUES(Table1[Year]),
"Please highlight a company or a product."
)
Regards,
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 |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |