cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RogierBI Frequent Visitor
Frequent Visitor

Determine monthly spend by two types of buyers

Hi all,

 

With some help from this forum I made a graph of the number of two types of buyers per month. One type of buyer buys 1 product multiple times a month and the other type of buyers buys various types of products each month. 

 

I'd like to now add a graph with the amount of money the buyers of single and multiple products spend each month.

See below an example of what the data would looks like.

I've tried adding extra data to the previously created measure for counting single and multi-users (Link to Forum). 

However, I only seem to be able to get the total sum and not a sum that changes by month.

 

Any advice on how to create a measure that sums the amount of money spend by buyers of single and multiple products so I can plot it as a graph per month?

 

Thanks,

Rogier

 

Cost for Single and Multiple Product Buyers.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Determine monthly spend by two types of buyers

Hi @RogierBI,

 

Based on my test, we can create another two new measures to meet your requirement.

 

MultipleBuyers1 = 
VAR ProductsPerBuyer = ADDCOLUMNS(SUMMARIZE(Data,Data[Buyer],Data[ProductType]),"CountProd",1)
VAR Buyers = GROUPBY(ProductsPerBuyer,[Buyer],"FinalCount",SUMX(CURRENTGROUP(),[CountProd]))
VAR SingleBuyers = FILTER(Buyers,[FinalCount]=1)
VAR MultipleBuyers = FILTER(Buyers,[FinalCount]>1)
RETURN
IF(ISBLANK(COUNTROWS(MultipleBuyers)),BLANK(),CALCULATE(SUM(Data[ Revenue ]),FILTER(Buyers,[FinalCount]>1)))
SingleBuyers1 = 
VAR ProductsPerBuyer = ADDCOLUMNS(SUMMARIZE(Data,Data[Buyer],Data[ProductType]),"CountProd",1)
VAR Buyers = GROUPBY(ProductsPerBuyer,[Buyer],"FinalCount",SUMX(CURRENTGROUP(),[CountProd]))
VAR SingleBuyers = FILTER(Buyers,[FinalCount]=1)
VAR MultipleBuyers = FILTER(Buyers,[FinalCount]>1)
RETURN
IF(ISBLANK(COUNTROWS(SingleBuyers)),BLANK(),CALCULATE(SUM(Data[ Revenue ]),FILTER(Buyers,[FinalCount]=1)))

Capture.PNG

For more details, please check the pbix as attached. If the above measure doesn’t work in your scenario, please share your expected result in table.

 

https://www.dropbox.com/s/zwwpzys3mr3ykgm/Determine%20monthly%20spend%20by%20two%20types%20of%20buye...

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Super User
Super User

Re: Determine monthly spend by two types of buyers

Sample data that can be copied and pasted would help. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

That being said, you should be able to create a column that essentially does an COUNTROWS or COUNT to classify each type of buyer. Then you just need to use that as your legend. Essentially, if you filter out ALL of each customer using EARLIER and the count of rows equals the number of months between the min and the max dates, then you know you have a single product buyer, otherwise it is the other class.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


RogierBI Frequent Visitor
Frequent Visitor

Re: Determine monthly spend by two types of buyers

Hi Greg,

 

Thanks for your reply. Not quite sure if I completely understand though.

I've already got a measure to determine the amount of buyers of single and multiple products (see below).

That measure uses COUNTROWS indeed.

However, I expected I should use SUM to determine the total revenue for each type of buyer.

I've added a table with Sample data this time :-).

 

Kind regards,

Rogier

 

SingleBuyers = 
VAR ProductsPerBuyer = ADDCOLUMNS(SUMMARIZE(Data,Data[Buyer],Data[ProductType]),"CountProd",1)
VAR Buyers = GROUPBY(ProductsPerBuyer,[Buyer],"FinalCount",SUMX(CURRENTGROUP(),[CountProd]))
VAR SingleBuyers = FILTER(Buyers,[FinalCount]=1)
VAR MultipleBuyers = FILTER(Buyers,[FinalCount]>1)
RETURN
COUNTROWS(SingleBuyers)
BuyerDateProductType Revenue 
Jake1-6-2018Appels €      2,00
Jake12-6-2018Bananas €      3,00
Jake18-6-2018Bananas €      3,00
Jake3-7-2018Appels €      2,00
Jake19-7-2018Bananas €      3,00
Jake20-7-2018Appels €      2,00
Jake1-8-2018Pears €      5,00
Erin4-6-2018Blueberries €      1,00
Erin13-6-2018Blueberries €      1,00
Erin18-6-2018Blueberries €      1,00
Erin4-7-2018Blueberries €      1,00
Erin20-7-2018Blueberries €      1,00
Erin2-8-2018Blueberries €      1,00

 

 

 

Super User
Super User

Re: Determine monthly spend by two types of buyers

Sure, with that you should just be able to use SUMX like this:

 

SingleBuyersSum = 
VAR ProductsPerBuyer = ADDCOLUMNS(SUMMARIZE(Data,Data[Buyer],Data[ProductType]),"CountProd",1,"TotalRevenue",SUM(Data[Revenue])))
VAR Buyers = GROUPBY(ProductsPerBuyer,[Buyer],"FinalCount",SUMX(CURRENTGROUP(),[CountProd]),"FinalTotal",SUMX(CURRENTGROUP(),[TotalRevenue]))
VAR SingleBuyers = FILTER(Buyers,[FinalCount]=1)
VAR MultipleBuyers = FILTER(Buyers,[FinalCount]>1)
RETURN
SUMX(SingleBuyers,[FinalTotal])

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


RogierBI Frequent Visitor
Frequent Visitor

Re: Determine monthly spend by two types of buyers

Thanks Greg, I've tried it out, but unfortunately this does not produce the desired result.

The result of your suggestion is for each month: (the number of SingleBuyers) x (the total Revenue in that month).

The goal was to find the revenue generated only by the SingleBuyers. 

 

Best regards,

Rogier

 

Community Support Team
Community Support Team

Re: Determine monthly spend by two types of buyers

Hi @RogierBI,

 

Based on my test, we can create another two new measures to meet your requirement.

 

MultipleBuyers1 = 
VAR ProductsPerBuyer = ADDCOLUMNS(SUMMARIZE(Data,Data[Buyer],Data[ProductType]),"CountProd",1)
VAR Buyers = GROUPBY(ProductsPerBuyer,[Buyer],"FinalCount",SUMX(CURRENTGROUP(),[CountProd]))
VAR SingleBuyers = FILTER(Buyers,[FinalCount]=1)
VAR MultipleBuyers = FILTER(Buyers,[FinalCount]>1)
RETURN
IF(ISBLANK(COUNTROWS(MultipleBuyers)),BLANK(),CALCULATE(SUM(Data[ Revenue ]),FILTER(Buyers,[FinalCount]>1)))
SingleBuyers1 = 
VAR ProductsPerBuyer = ADDCOLUMNS(SUMMARIZE(Data,Data[Buyer],Data[ProductType]),"CountProd",1)
VAR Buyers = GROUPBY(ProductsPerBuyer,[Buyer],"FinalCount",SUMX(CURRENTGROUP(),[CountProd]))
VAR SingleBuyers = FILTER(Buyers,[FinalCount]=1)
VAR MultipleBuyers = FILTER(Buyers,[FinalCount]>1)
RETURN
IF(ISBLANK(COUNTROWS(SingleBuyers)),BLANK(),CALCULATE(SUM(Data[ Revenue ]),FILTER(Buyers,[FinalCount]=1)))

Capture.PNG

For more details, please check the pbix as attached. If the above measure doesn’t work in your scenario, please share your expected result in table.

 

https://www.dropbox.com/s/zwwpzys3mr3ykgm/Determine%20monthly%20spend%20by%20two%20types%20of%20buye...

 

Regards,

Frank

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

Re: Determine monthly spend by two types of buyers

It works!

It became a nice and simple solution with that last line.

Thanks a lot!