Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am analysing expenditure. I want to use a pie chart to show % expenditure, but there are too many groups:
I want to keep the largest 6 groups, then group the remaining as "Other":
I want this group to be dynamic, so that if I change it to Income, it will perform a similar operation.
Something with the following logic:
If [Group] is not one of the TopN, then "Other", else [Group]
Anyone know how this can be done?
Thanks!
Big Thanks to ZhongTr0n for his solution : https://towardsdatascience.com/power-bi-add-category-other-to-charts-354267ec1809
Hi,
I have the similar requirement .I have got my top N. However I could not get "Others" group.
I would be grateful if someone can please help me.
Thanks!
I have tried the following formulas:
Based on TopN:
Dynamic Group = IF ( CONTAINS ( TOPN ( 5, SUMMARIZE ( 'Table1', [Group], "Sum Amount", SUM ( 'Table2'[Amount] ) ), [Sum Amount] ), 'Table1'[Group], 'Table1'[Group] ) = TRUE (), [Group], "Other" )
Based on Top %:
Dynamic Group = IF ( [%] < 0.05, "Other", [Title A] )
Both work, but they don't respond to slicers or filters, so are not dynamic.
Any thoughts?
It might be a case of working in a FILTER by ALLSELECTED function somewhere, but I have tried a few different combinations and all end up with an error.
Hi @bullius,
In order to get Other group, we can use RANKX function to achieve this requirement.
NewGroup= IF(RANKX(Table3,Table3[Amount])<=5,Table3[Category],"Others")
Regards,
Charlie Liao
Thanks @v-caliao-msft,
I can't seem to fit my data into your formula.
My data is set up like the example below:
'Transactions'
Transaction | Code | Group | Type | Amount £ |
A11 | A1 | A | Income | 11 |
A12 | A1 | A | Income | 11 |
A21 | A2 | A | Income | 55 |
A22 | A2 | A | Income | 55 |
A31 | A3 | A | Income | 22 |
A32 | A3 | A | Income | 22 |
B11 | B1 | B | Income | 88 |
B12 | B1 | B | Income | 88 |
B21 | B2 | B | Income | 66 |
B22 | B2 | B | Income | 66 |
B31 | B3 | B | Income | 33 |
B32 | B3 | B | Income | 33 |
C11 | C1 | C | Income | 2 |
C12 | C1 | C | Income | 2 |
D11 | D1 | D | Income | 6 |
D12 | D1 | D | Income | 6 |
E11 | E1 | E | Income | 10 |
E12 | E1 | E | Income | 10 |
F11 | F1 | F | Expenditure | 10 |
F12 | F1 | F | Expenditure | 10 |
F21 | F2 | F | Expenditure | 50 |
F22 | F2 | F | Expenditure | 50 |
F31 | F3 | F | Expenditure | 20 |
F32 | F3 | F | Expenditure | 20 |
G11 | G1 | G | Expenditure | 80 |
G12 | G1 | G | Expenditure | 80 |
G21 | G2 | G | Expenditure | 60 |
G22 | G2 | G | Expenditure | 60 |
G31 | G3 | G | Expenditure | 30 |
G32 | G3 | G | Expenditure | 30 |
H11 | H1 | H | Expenditure | 1 |
H12 | H1 | H | Expenditure | 1 |
I11 | I1 | I | Expenditure | 3 |
I12 | I1 | I | Expenditure | 3 |
J11 | J1 | J | Expenditure | 5 |
J12 | J1 | J | Expenditure | 5 |
'Chart_of_Accounts'
Code | Group | Group Name |
A1 | A | Aa |
A2 | A | Aa |
A3 | A | Aa |
B1 | B | Bb |
B2 | B | Bb |
B3 | B | Bb |
C1 | C | Cc |
D1 | D | Dd |
E1 | E | Ee |
F1 | F | Ff |
F2 | F | Ff |
F3 | F | Ff |
G1 | G | Gg |
G2 | G | Gg |
G3 | G | Gg |
H1 | H | Hh |
I1 | I | Ii |
J1 | J | Jj |
With this example, the visualised data looks like this:
I would want groups Jj, Ii and Hh to be grouped as "Other".
Also, it would need to be subject to the Type slicer.
Could you see if you can adjust the RANKX formula to accomodate this data setup?
Thanks!
I tested it on my locla environment by using your sample data.
Firstly, create a new table:
Table = SUMMARIZE('Transaction','Transaction'[Group],'Transaction'[Type],"TotalAmount",SUM('Transaction'[Amount £]))
Then get group name from Chart_of_Accounts table
GroupName = LOOKUPVALUE(Chart_of_Accounts[Group Name],Chart_of_Accounts[Group],'Table'[Group])
Then use RANK function to get others group.
NewGroupName = IF(RANKX(FILTER('Table','Table'[Type]=EARLIER('Table'[Type])),'Table'[TotalAmount])<=2,'Table'[GroupName],"Others")
Regards,
Charlie Liao
Thanks @v-caliao-msft. That works perfectly with the example data I gave. The key being the EARLIER function, which I had not considered.
However, I want to go a step further.
My data contains more columns that I want to use to filter the data. I didn't include them in the example because I wanted to keep it simple and hoped the solution would work with more than the example columns.
The problem is, If I use the EARLIER function to group the ranked data by each given column, I will need a NewGroupName column for every concievable filter combination.
Ultimately, my question is this: Can the values in a calculated column change based on slicer selection?
If they can, then I would need a formula for the NewGroupName column that would give the TopN Groups and "Others" group, whether I am filtering by Income / Expenditure, any of the other columns, or a combination.
If they can't, then I will make it work with your NewGroupName formula, using the EARLER function.
Thank you for bearing with me.
Hello Evryone,
Some one have a news about this topic ?
Best Regards
No progress, sorry.
Have you tried grouping or binning?
https://docs.microsoft.com/en-us/power-bi/desktop-grouping-and-binning
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |