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

Show duplicate data

Hi All,

 

I have the following data

 

Date        Customer name    product purchased     Amount spent

1/1/2017   David                       A                                   173

1/11/2017  Steve                       A                                    164

1/20/2017   Victor                     N                                     171

1/31/2017    David                     S                                    156

2/5/2017       Mark                    P                                   148

.

.

.

And so on a total of 200 rows with multiple duplicate value

 

I want to create a funnel chart which shows the top 10 spending customers irrespective of date or product

 

The reslut shoul be

 

Customer name  Amount PSent

David                    173

Victor                     171

Steve                     164

David                     156

Mark                     148 and so on

 

Could anyone help me on this

 

2 REPLIES 2
Highlighted
Moderator v-sihou-msft
Moderator

Re: Show duplicate data

@baronraghu

 

In this scenario, do you want to keep duplicate rows for same customer in your result set? Or you want to have the Amount spend aggregate on customer first. Then select the TOP 10 users?

 

If you want to keep the duplicates for same customer, you just need to use TOPN() on this table. 

 

 

Calculated Table = TOPN(10,Table,Table[Amount Spent])

If you need to aggregate your table on customer level.

 

Users Amount Spent= SUMMARIZE(Table,Table[Customer],"Total Amount Spent",CALCULATE(SUM(Table[Amount Spent])))

Then user TOPN on above calculated table.

 

Calculated Table = TOPN(10,'Users Amount Spent','User Amount Spent'[Total Amount Spent])

Regards,

 

 

 

baronraghu Member
Member

Re: Show duplicate data

@v-sihou-msft

 

thanks for your prompt reply.

 

I would like the keep duplicate rows for same customer in result set. I just want to know what were the top 10 spending amount irrespective of the date and who where those customers.

 

I tried the TopN function as suggested by you. I am getting this error  "The expression refers to multiple coulmn. Multiple coulmn cannot be converted into scalar value.

 

Thanks

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 179 members 1,854 guests
Please welcome our newest community members: