Reply
Member
Posts: 47
Registered: ‎01-11-2017
Accepted Solution

Display duplicate

[ Edited ]

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

 


Accepted Solutions
Community Support Team
Posts: 5,409
Registered: ‎09-21-2016

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.

View solution in original post

Highlighted
Community Support Team
Posts: 5,409
Registered: ‎09-21-2016

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.

View solution in original post


All Replies
Community Support Team
Posts: 5,409
Registered: ‎09-21-2016

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.
Member
Posts: 47
Registered: ‎01-11-2017

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

Highlighted
Community Support Team
Posts: 5,409
Registered: ‎09-21-2016

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.