Raffle Ticket Number Generator

[newgirl]

Hi, guys!

Hope you could help me with this one. Our team was assigned to develop a report for a rewards program for our clients, something like if client has transacted 10 times in a month, they earn 1 raffle entry.

So anywy, here's a sample of our data below:

Client	Status	Date	Segment
5000123	Pick-up	Jan-20	Retailer
5000124	Delivered	Feb-20	Wholesaler
5000125	Pick-up	Mar-20	Wholesaler
5000123	Delivered	Jan-20	Retailer
5000124	Pick-up	Mar-20	Wholesaler
5000125	Pick-up	Feb-20	Wholesaler
5000123	Pick-up	Mar-20	Retailer
5000124	Pick-up	Jan-20	Wholesaler
5000125	Delivered	Mar-20	Wholesaler
5000123	Delivered	Feb-20	Retailer
5000124	Pick-up	Feb-20	Wholesaler
5000125	Pick-up	Feb-20	Wholesaler
5000123	Pick-up	Mar-20	Retailer
5000124	Delivered	Mar-20	Wholesaler
5000125	Delivered	Mar-20	Wholesaler

And the conditions of the program are:

- for each pick-up transaction, they earn 1 raffle ticket.

- for each month that they have 100% pick-up transactions they earn additional 5 tickets.

So below data is the expected report in PBI. I don't have a problem in this part since I kind of have an idea on the measures to be used for this.

This is our  problem. We need to be able to generate a list/table that would generate raffle ticket numbers based on the value of the measure output.

So in the sample below, since client 5000123 has earned 7 raffle tickets for the month of January, there are 7 rows for client 5000123 for the month of January. Below is the full example, in which I color-coordinated to give an idea on the basis of the number of generatd rows. At the end of the program, the team who requested this report would need to download the list since they would input the raffle entry ticket numbers in an online randomizer. 

We're thinking of having the raffle entry number a CONCATENET of Client, YearMonth, and the order number of the raffle entry for easy formula.

Now the question is how can we generate the said table or list? Please help! 

[lbendlin]

create two calculated columns

Pickup = if( Raffle[Status]="Pick-up",1,0)

Delivered = if(Raffle[Status]="Delivered",1,0)

Create a measure

Tickets = sum(Raffle[Pickup])+ if(sum(Raffle[Pickup])=sum(Raffle[Delivered]),5,0)

As you can see there is an issue in the last row. You want to be more specific when you say " for each month that they have 100% pick-up transactions they earn additional 5 tickets."  

what does 100% pick-up mean? what if it is over 100% ?