Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
In my Pie chart I have selected Top 20 based on Count of UOM. What do I do to display top 11 to 20 values in my pie chart( i.e. from HKG to POL).
Hi @Pragati
Try the following :
1. Create a measure ,say RankByCountry, to compute the Rank by country by count of UOM or any other measure you require.
2. In the pie chart use this in the Filter section of the pie chart and set it to value greater than equal to 11 and less than equl to 20 .
3. This should do the job.
A sample screen shot based on item sold quantity.
If this works for you please accept it as a solution and also give KUDOS.
Cheers
CheenuSing
I would personally consider a measure that finds the difference between the top 20 and top 10 countries, and applies that as a filter (intersected with the current country).
Something like:
%GT Qty for top 11-20 = VAR Top20Country = TOPN ( 20, ALL ( Table[Access Country] ), CALCULATE ( COUNTA ( Table[UOM] ) ) ) VAR Top10Country = TOPN ( 10, ALL ( Table[Access Country] ), CALCULATE ( COUNTA ( Table[UOM] ) ) ) VAR Top11To20Country = EXCEPT ( Top20Country, Top10Country ) RETURN CALCULATE ( [%GT Qty], Top11To20Country, VALUES ( Table[Access Country] ) )
Also, with a measure like this, you don't need the visual level filter.
Regards,
Owen
I had a similar query. Let me know if you get a solution
Hi, use this Measure in Values Section of your visual (mantaining the top 20 filter)
SalesFiltered11to20 = IF ( COUNTROWS ( INTERSECT ( VALUES ( Table1[Code] ), TOPN ( 10; ALL ( Table1[Code] ), CALCULATE ( SUM ( Table1[Sale] ) ) ) ) ) > 0, BLANK (), SUM ( Table1[Sale] ) )
Thank you for your response.
It is not giving me the desired output. I wanted Top 11-20 values.
Hi @Pragati,
You can create a new table display the top 11-top20 values, then use the new table to create visual as follows.
1. Create a new table using the formula.
NewTable = INTERSECT(TOPN(10,SUMMARIZE(Table,Table[Access Country],"Count of UON",COUNTA(Table[UOM]),"%GT Qty",[%GT Qty]),[Count of UOM]),TOPN(20,SUMMARIZE(Table,Table[Access Country],"Count of UON",COUNTA(Table[UOM]),"%GT Qty",[%GT Qty]),[Count of UOM]))
2. Create a pie chart use the 'NewTable', select the [Access Country] as Legend, the [%GT Qty] as value, please check if it works fine.
If this still resolves your issue, please share your sample table, or .pbix file for further analysis.
Best Regards,
Angelia
Hi,
The requirement has changed.
I want Top 11 to 20 'Access Country' based on sum of 'Qty' (i.e. from 'HKG' to 'JPN' to be displayed in Pie Chart)
Legend: 'Access Country'
Values: Percentage of grand Total of Sum of 'Qty'.
Table name: Sheet1
Column Name: Access Country, Qty
Hi @Pragati,
Please create a new table using the formula below.
NewTable = INTERSECT ( TOPN ( 10, SUMMARIZE ( Sheet1, Sheet1[Access Country], "Percentage", Sheet1[Qty] / SUM ( Sheet1[Qty] ) ), [Percentage] ), TOPN ( 20, SUMMARIZE ( Sheet1, Sheet1[Access Country], "Percentage", Sheet1[Qty] / SUM ( Sheet1[Qty] ) ), [Percentage] ) )
Then select the 'Access Country' as Legend, the [Percentage] as value, you will get expected result.
Best Regards,
Angelia
Hi,
It is giving me an error.
Following is the measure:
NewTable =
INTERSECT (
TOPN (
10,
SUMMARIZE (
Sheet1,
Sheet1[Access Country],
"Percentage", SUM(Sheet1[Qty])
),
[Percentage]
),
TOPN (
20,
SUMMARIZE (
Sheet1,
Sheet1[Access Country],
"Percentage", SUM(Sheet1[Qty])
),
[Percentage]
)
)
Error:
"A single value for column 'Qty' in table 'Sheet1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.''
Hi @Pragati
Appreciate if you can be more specific about the error.
What was the measure or column you were doing giving the error.
Cheers
CheenuSing
Hi @Pragati,
Your Sheet1[Qty] is a column, which may cause the issue. Please create a new table using the formula.
NewTable = INTERSECT ( TOPN ( 10, SUMMARIZE ( Sheet1, Sheet1[Access Country], "Qty", SUM ( Sheet1[Qty] ) ), [Percentage] ), TOPN ( 20, SUMMARIZE ( Sheet1, Sheet1[Access Country], "Qty", SUM ( Sheet1[Qty] ) ), [Percentage] ) )
Then create a calculated column using the formula.
percentage=Sheet1[Qty]/SUMX(ALL(Sheet1),Sheet1[Qty])
Best Regards,
Angelia
Hi,
When I am creating a new table it is giving me an exception:
"Column 'Percentage' cannot be found or may not be used in this expression."
and after adding percentage column it is giving me an exception:
"A single value for column 'Qty' in table 'Sheet1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
Hi @Pragati,
Please change your [percentage] as follows, and check if it works fine. Do you mind share your .pbix file for further analysis?
percentage=SUM(Sheet1[Qty])/SUMX(ALL(Sheet1),Sheet1[Qty])
Best Regards,
Angelia
How do I attach my .pbix file here?
Hi @Pragati,
Have you resolved your issue? If you have, please mark the right and helpful reply as answer, more people will benefit from here.
Best Regards,
Angelia
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |