Forum Discussion

TheCigi9's avatar
TheCigi9
Frequent Visitor
10 years ago
Solved

Rank Visualization and rounddown

     as you see this is What I'd like to solve, today.   I'greatly appreciate your help.   Thanks  
Show and Tell
  • v-sihou-msft's avatar
    v-sihou-msft
    10 years ago

    TheCigi9

     

    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:

    1. Create a calculated column which stores the value rank of each product in the company.
      RankInCompany1 = 
RANKX (
    FILTER ( Table1, EARLIER ( Table1[Selling company] ) = Table1[Selling company] ),
    Table1[Value]
)
    2. Create a calculated column which stores the information displayed in Multi-row card.
      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) & "€" )
    3. Create a measure which will show the most expensive product of selected company.
      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."
)
    4. Drag one custom visual Hierarchy Slicer, one Multi-row card and one Card into the canvas.
v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
10 years ago

TheCigi9

 

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.

 

  1. Create a Year column as below.
    Year = 
YEAR ( Table1[Date] )
  2. Create a calculated column which stores the value rank of each product in the company per year.
    RankInCompanyPerYear = 
RANKX (
    FILTER (
        Table1,
        EARLIER ( Table1[Selling company] ) = Table1[Selling company]
            && EARLIER ( Table1[Year] ) = Table1[Year]
    ),
    Table1[Value]
)
  3. Create a calculated column which stores the information displayed in Multi-row card.
    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])
  4. Create a measure which will show the most expensive product of selected company per 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."
)
  5. Drag one custom visual Hierarchy Slicer (Selling company and Brand name in Fields), one slicer (Year in Filed), one Multi-row card (TitlePerYear in Fields) and one Card (MostExpensiveCarPerYear in Fields) into the canvas.
TheCigi9's avatar
TheCigi9
Frequent Visitor
10 years ago

@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

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    10 years ago

    TheCigi9

     

    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,