Help needed to Calculate repeat sales as number of times sold

[racs]

Hi, 

I'm trying to calculate how many times a product is sold over the months in the order at least 1 time, 2+ times, 3+ 

In my Sales data table, I have column for "# of times Sold". I created a Matrix visualization with following parameters:

Rows:  "# of Times Sold"

Columns: "Month"

Values: Count of # of Times Sold

Below is the Output of this visualization.

Sample Sales Data Table:

Month	# of Times Sold
Jan	1
Jan	2
Jan	2
Jan	3
Jan	3
Jan	3
Jan	4
Jan	1
Jan	2
Jan	3
Jan	3
Jan	5
Jan	1
Jan	4
Jan	4

Example Current Output (Matrix Visualization):

# of Repeat Sales	Jan
1	4
2	3
3	5
4	3
5	2
Total	17

However, I'm a beginner and need help to create the following output.

Here, assuming everything is atleast sold once, so

Atleast 1 time = total count of # of Times Sold"

2+ times = Count of "# Times Sold" with column values >=2

3+ times = Count of "# Times Sold" with column values >=3

and so on

Example Desired Output:

# of Repeat Sales	Jan
At least 1 time	17
2+ times	13
3+ times	10
4+ times	5
5+ times	2

Any help or guidance regarding this is greately appreciated. 

Thank you.

[FreemanZ]

hi @racs 

try to plot a matrix visual with the two columns and a measure like:

measure = 
COUNTROWS(
    FILTER(
        ALL(data),
        data[# of Times Sold] >= MAX(data[# of Times Sold])
    )
)

it worked like:

p.s. your dataset seems lacking one row for 1 and 5 each.

[racs · Accepted solution]

Hi @FreemanZ

Thanks for your reply.

I tried creating matrix visualization with this measure, doesn't seem to work for me.

I was somehow able to create the desired output by using following steps:

1. I created separate measures each for
   • Sold atleast 1 time = Count('Sales'[# of Times Sold])
   • Sold 2+ times = Calculate(Count('Sales'[# of Times Sold]), 'Sales'[# of Times Sold]>=2)
   • and so on
2. I created a matrix visualization with following parameters:
   • Columns: Month
   • Values:
     1. Sold at least 1 time
     2. Sold 2+ times
     3. Sold 3+ times
     4. Sold 4+ times
     5. Sold 5+ times
   • Under Format Paint Settings > Values > Options > Switch values to rows (On)