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.

5 Replies

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    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.
    • TheCigi9's avatar
      TheCigi9
      Frequent Visitor
      10 years ago

      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

      • 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.