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
v-yulgu-msft Super Contributor
Super Contributor

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

v-yulgu-msft Super Contributor
Super Contributor

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

3 REPLIES 3
v-yulgu-msft Super Contributor
Super Contributor

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

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

v-yulgu-msft Super Contributor
Super Contributor

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 433 members 4,173 guests
Please welcome our newest community members: