cancel
Showing results for
Did you mean:
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

1 ACCEPTED SOLUTION

Accepted Solutions
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 =
RETURN
```SingleBuyers1 =
RETURN

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

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

## 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.

Proud to be a Datanaut!

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 =
RETURN
 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

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 =
RETURN

Proud to be a Datanaut!

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

## 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 =
RETURN
```SingleBuyers1 =
RETURN

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

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.
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!