Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I've got a Line and Stacked Column Chart (using this because of Pareto line). I used to create a measure "Count".
Count = CALCULATE ( COUNTROWS ( Projects ), ALLSELECTED ( Projects ), VALUES ( Projects[Points] ) )
"Project[Points]" is the Shared axis and "Count" is the Column values.
Now I want to use this measure and combine any calculated values below 3 into a miscellaneous/other group. So it would be removing "M" thru "g", and creating an "other", stacking them all together.
I've been using power bi for about 2 months now. So the more steps explained the better.
Thank you.
Solved! Go to Solution.
Hi @Geilisa ,
Try this:
1. Create a table used for shared axis and DO NOT create relationships between this table and your fact table.
PointGroup =
UNION ( DISTINCT ( Projects[Points] ), ROW ( "Others", "Others" ) )
2. Create a What-if parameter for threshold.
3. Create measures.
ModifiedCount =
VAR CurPoint_ =
MAX ( PointGroup[Points] )
RETURN
CALCULATE ( COUNTROWS ( Projects ), Projects[Points] = CurPoint_ )
ModifiedCount 2 =
VAR threshold_ = [threshold Value]
VAR CurPoint_ =
MAX ( PointGroup[Points] )
RETURN
IF (
[ModifiedCount] > threshold_,
[ModifiedCount],
IF (
CurPoint_ = "Others",
SUMX (
FILTER ( ALLSELECTED ( PointGroup ), [ModifiedCount] <= threshold_ ),
[ModifiedCount]
)
)
)
4. Create a visual.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for your reply. I ended up doing a string of if statments to just rename the project names to "other" manually, unfortunetly this wont auto update based on if its lessthan or equal to 3. But it works for now...
Hi:
The other way is to Rclick the field on the right (columns with the values you want to consolidate) and choose New Group.
Here's an image you can bucket values or other fields this way.
I see this does work, but it isnt exactly what I'm looking for. Hoping to make it automatic. But yes it does work for grouping. Took me a little experimenting.
Hi @Geilisa ,
Try this:
1. Create a table used for shared axis and DO NOT create relationships between this table and your fact table.
PointGroup =
UNION ( DISTINCT ( Projects[Points] ), ROW ( "Others", "Others" ) )
2. Create a What-if parameter for threshold.
3. Create measures.
ModifiedCount =
VAR CurPoint_ =
MAX ( PointGroup[Points] )
RETURN
CALCULATE ( COUNTROWS ( Projects ), Projects[Points] = CurPoint_ )
ModifiedCount 2 =
VAR threshold_ = [threshold Value]
VAR CurPoint_ =
MAX ( PointGroup[Points] )
RETURN
IF (
[ModifiedCount] > threshold_,
[ModifiedCount],
IF (
CurPoint_ = "Others",
SUMX (
FILTER ( ALLSELECTED ( PointGroup ), [ModifiedCount] <= threshold_ ),
[ModifiedCount]
)
)
)
4. Create a visual.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi:
Without seeing your data and tables I can guess if you want to group your measure for points under three, you could try"
Group ❤️ = CALCULATE([Count], FILTER(Projects, Projects[points] <= 3)) **where [Count is your existing measure)
You might need to subtract this value from your Count measure if you are trying to separate ❤️ and >3.
I hope this helps!
I don't know how hearts appeared, I try to bold measure...
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |