Forum Discussion

baronraghu's avatar
baronraghu
Helper III
8 years ago
Solved

Display duplicate

Hi All,

 

I have a Table as shown below

 

ProductBuyers
AX
AY
AZ
AM
BX
BY
BK
BP
BL
CX
CZ
CM
CK
DZ
DX
DP

 

I have created a Top N function using the "New Parameter What IF" that tells me how many buyers were there against a particular product

 

I have used this function RANKX(ALLSELECTED(bidder1[Product Name),[total bids]='Top N'[Top N Value])-1 for that purpose

 

So if in the Top N slicer I select 3 then the the products with 3 buyer are 1 is dispalyed.

 

But now i want a table that shows the product and the name of the buyers. HOw should i do this.

 

If in the top N slider i select 4 i.e. I want to know how many products were bought by 4 buyers then a table should display the name of products and the name of the buyers, like this

 

CX
 Z
 M
 K

 

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi baronraghu,

     

    Based on my assumption, the formula of [total bids] should be:

    total bids = CALCULATE(COUNT(bidder1[Buyers]),ALLEXCEPT(bidder1,bidder1[Product]))

     

    Then, create an extra measure like this, add [check1] to table visual and set its value to 1.

    check1 = IF([total bids]='Top N'[Top N Value],1,0)

     

    Best regards,

    Yuliana Gu

  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi baronraghu,

     

    Please refer to this document to better understand ALLEXCEPT() function.

     

    It is not possible to avoid duplicating values in table visual to get above result. As a workaround, you can use a Matrix instead.

    Add [Product] and [Buyer] into "Rows" section of Matrix. Click the third "Drill Down" icon. Turn off "Stepped Layout" option.

     

    Best regards,

    Yuliana Gu

3 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    Hi baronraghu,

     

    Based on my assumption, the formula of [total bids] should be:

    total bids = CALCULATE(COUNT(bidder1[Buyers]),ALLEXCEPT(bidder1,bidder1[Product]))

     

    Then, create an extra measure like this, add [check1] to table visual and set its value to 1.

    check1 = IF([total bids]='Top N'[Top N Value],1,0)

     

    Best regards,

    Yuliana Gu

    • baronraghu's avatar
      baronraghu
      Helper III
      8 years ago

      Thanks Yulina (@v-yulgu-msft)

       

      The solution offered worked great. Just didnt understand the purpose of ALLEXCEPT in the formula.

       

      Also is it possible to have a Product name not repeated in the table for multiple buyers. Illustration below

       

      Product Buyer

      A             X

      A            M
      A            T

      A            P

      A            L

      A            E

       

      I want to show my Data as 

      Product Buyer

      A             X

                     M

                     T

                     P

                     L

                     E

       

      This way i feel it looks clean to see. Also I am open to any better visual/ recomendation 

       

      Thanks

      Raghu

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Microsoft Employee
        8 years ago

        Hi baronraghu,

         

        Please refer to this document to better understand ALLEXCEPT() function.

         

        It is not possible to avoid duplicating values in table visual to get above result. As a workaround, you can use a Matrix instead.

        Add [Product] and [Buyer] into "Rows" section of Matrix. Click the third "Drill Down" icon. Turn off "Stepped Layout" option.

         

        Best regards,

        Yuliana Gu