Double grouping in visuals (no Power Query)

[eugenm]

Hi there, how would one solve this sort of problem?
I want to have a filtered visual for the following:

DATA

Clowns	Balls	Color
ClownA	1	Red
ClownB	2	Blue
ClownC	3	Green
ClownD	4	Green
ClownE	4	Yellow
ClownF	4	Red
ClownG	2	Red
ClownH	2	Blue
ClownI	1	Red
REPORT & VISUAL
Desired output (available filter: Color)
# of Clowns with 1 Ball	2
# of Clowns with 2 Balls	3
# of Clowns with 3 Balls	1
# of Clowns with 4 Balls	3

Not sure how you'd call this sort of "binning", but this is the desired logic.

I want to be able to tell how many clowns are in each category, with the data being filterable by color.
In other words, when applying a red color filter, the output will indicate the # of clowns with red balls, within each category.

Ideally, I want to achieve this without the use of a power query, as I am dealing with a massive amount of data and several other calculation, as well as remote data... 
No matter what, it must be possible to plot a graph that plots the result as per the output table above, impacted by a color filter.

Thanks in advance,

Eugen

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[eugenm]

So, the idea is that the clowns get their balls in batches, at different times, not all at once.

[eugenm]

Ah, never mind, that's over complicating it.
I'm sure that I won't need to make that upper limit dynamic after all.

Thanks again. This works.

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[eugenm]

Thank you very much. 
Almost there, but how can this be done if the # of categories has to be dynamic and potentially much higher (in the tens)? Obviously that would not be colors, as in this example. Let's say that it's numbered balls... But at the beginning we don't know what's the max # of categories that we will have at the beginning of the calculation.
Thoughts? In reality, in my real life case it looks like I will have 14 categories, but I'd like to know how to handle this if that changes a lot, as it looks like it may.

Thank you

[Ashish_Mathur]

You are welcome.

[eugenm]

If you don't mind, may I add another angle, which is actually a direct reflection of my data. I know how to do this the harder way, but I feel like your formula can be adjusted to include this sort of situation as well.

The idea here is that the clowns get their balls every now and then (earn them in a contest lol).
How can your formula be adjusted to account for this data distribution?
Date         Clowns    Balls       Color

01-May-24	ClownA	1	Red
02-May-24	ClownB	1	Blue
02-May-24	ClownB	1	Blue
01-May-24	ClownC	1	Green
03-May-24	ClownC	2	Red
02-May-24	ClownD	2	Green
06-May-24	ClownD	1	Yellow
06-May-24	ClownD	2	Blue
01-May-24	ClownE	4	Yellow
04-May-24	ClownF	4	Red
03-May-24	ClownG	2	Red
05-May-24	ClownH	1	Blue
04-May-24	ClownH	1	Yellow
03-May-24	ClownI	1	Red

Thank you kindly,

Eugen

[Ashish_Mathur]

Neither have i understood your question nor do i know the expected result you want.  Therefore, even if i want to, i cannot help.  Show the expected result in a Table format very clearly with a proper explanation.

[eugenm]

Certainly. You're right.
Here's the full scoop:

Data

Date	Clowns	Balls	Color
01-May-24	ClownA	1	Red
02-May-24	ClownB	1	Blue
02-May-24	ClownB	1	Blue
01-May-24	ClownC	1	Green
03-May-24	ClownC	2	Red
02-May-24	ClownD	2	Green
06-May-24	ClownD	1	Yellow
06-May-24	ClownD	2	Blue
01-May-24	ClownE	4	Yellow
04-May-24	ClownF	4	Red
03-May-24	ClownG	2	Red
05-May-24	ClownH	1	Blue
04-May-24	ClownH	1	Yellow
03-May-24	ClownI	1

Intermediate Calculation/ View
(it shows the ball totals that each clown has, irrespective of ball color)

Clown	Total Balls
ClownA	1
ClownB	2
ClownC	3
ClownD	5
ClownE	4
ClownF	4
ClownG	2
ClownH	2
ClownI	1

DESIRED OUTPUT
(The Category Based Report)

Category	Clowns by Category
1 ball	2
2 balls	3
3 balls	1
4 balls	2
5 balls	1

I hope that clear the use case and the desired outcome.

Thanks again.

[Ashish_Mathur]

My solution will work here.  What problem are you facing with my solution?

[eugenm]

Is it possible to do the sum of balls from the various dates, for each clown, within the same formulas that you've used, or do I have to do that summing up, per clown , to get their ball total - separately?

[Ashish_Mathur]

I am confused.  In the desired outcome section of your previous message, there is no mention of Date anywhere.  What results do you get with my solution?  Are those results wrong?  What result do you want to see?

[eugenm]

Your solution covers the processing from the intermediate view to the end result (the last two tables). The actual input data though comes broken down by dates of when the clowns get the balls. 
There is no date in the results and it's not required.
But the number of balls per clown is not known from the beginning. It has to be gathered from the first data input, that shows when they've got the balls. 
It's not important when they've got the balls, but how many they got.
But the how many comes by adding the balls received during the respective dates, gradually. 

I really hope that this clarifies it better.

[Ashish_Mathur · Accepted solution]

My solution will not be affected by an additoinal Date column in Table1.

[eugenm]

Hang on, I guess that it may work. I haven't tried it with the first table as input.
It may not matter that you have the data ,sorry. I now see what you mean.

Let me double check. 

[eugenm]

Indeed, upon double checking the formulas, I agree that it shouldn't make a difference.

There still is one issue. In the new data, I have a total of 5 balls for one of the clowns. 
It does not come out in the result (more than 4 balls = 0, instead of 1).

[eugenm]

Nevermind. Fixed. It was a copy and paste error, as I duplicated a table when creating the new data source. 
It's all good. Thank you so much. It works with this new data as well. 

[Ashish_Mathur]

Should work absolutely fine.  If it is working fine for the other categories, there is no reason for it to fail for 1.  Check thorughly.

DIY.