Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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)))
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.
Regards,
Frank
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.
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)
Buyer | Date | ProductType | Revenue |
Jake | 1-6-2018 | Appels | € 2,00 |
Jake | 12-6-2018 | Bananas | € 3,00 |
Jake | 18-6-2018 | Bananas | € 3,00 |
Jake | 3-7-2018 | Appels | € 2,00 |
Jake | 19-7-2018 | Bananas | € 3,00 |
Jake | 20-7-2018 | Appels | € 2,00 |
Jake | 1-8-2018 | Pears | € 5,00 |
Erin | 4-6-2018 | Blueberries | € 1,00 |
Erin | 13-6-2018 | Blueberries | € 1,00 |
Erin | 18-6-2018 | Blueberries | € 1,00 |
Erin | 4-7-2018 | Blueberries | € 1,00 |
Erin | 20-7-2018 | Blueberries | € 1,00 |
Erin | 2-8-2018 | Blueberries | € 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])
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)))
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.
Regards,
Frank
It works!
It became a nice and simple solution with that last line.
Thanks a lot!
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |