Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RogierBI
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

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 others find it more quickly.

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

 

 

 

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])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

 

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 others find it more quickly.

It works!

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

Thanks a lot!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.