The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to Solution.
Hi,
PBI file attached.
Hope this helps.
My solution will not be affected by an additoinal Date column in Table1.
So, the idea is that the clowns get their balls in batches, at different times, not all at once.
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.
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
You are welcome.
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
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.
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.
My solution will work here. What problem are you facing with my solution?
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?
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?
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.
My solution will not be affected by an additoinal Date column in Table1.
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.
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).
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.
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
93 | |
81 | |
63 | |
56 |
User | Count |
---|---|
247 | |
122 | |
110 | |
77 | |
72 |