cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
baronraghu Member
Member

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

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

Re: Display duplicate

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)

 

1.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: Display duplicate

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.

3.PNG1.PNG2.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Highlighted
Community Support Team
Community Support Team

Re: Display duplicate

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)

 

1.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
baronraghu Member
Member

Re: Display duplicate

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

Community Support Team
Community Support Team

Re: Display duplicate

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.

3.PNG1.PNG2.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.