Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Pragati
Frequent Visitor

top 11 to 20

PieChart.jpgTop 11 to 20.jpg

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).

16 REPLIES 16
CheenuSing
Community Champion
Community Champion

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.

 

Capture12.GIF

 

If this works for you please accept it as a solution and also give KUDOS.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
OwenAuger
Super User
Super User

@Pragati

 

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Arfaa
New Member

I had a similar query. Let me know if you get a solution

Vvelarde
Community Champion
Community Champion

@Pragati

 

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] )
)

  




Lima - Peru

PieChart.jpg

 

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

 

Required.jpg

 

 

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

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

Please upload the file in the Attachments in the following screenshot.

1.PNG

Or you can upload your .pbix file to Onedrive and post the shared link here.

Best Regards,
Angelia

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.