Filter Values for Pie Chart

[Anonymous]

Hi,

I am looking for the a solution to the following:-

I want to create a pie chart to show 3 elements:-

1st Element to show values of projects worth 0-5 Million (data field is totalvalue)

2nd Element to show values of projects worth 5-20 Million (data field is total value)

3rd Element to show values of projects worth 20 Million and above (data field is total value)

Each project is identified with an individual project number (data field project number)

Thanks

[msksenthil]

@Anonymous Can you try with the calculated column option? 

[BA_Pete]

Hi @Anonymous ,

Can you provide a sample of your data please?

The method of achieving this will be different based on your data and structure.

Ta,

Pete

[Anonymous]

Hi Pete,

Thanks. Yes sure. Here is my data below:-

Project Number is held in a table called Projects and Total Value is held in a table called Financial:-

Thanks

[BA_Pete]

Cool, thanks. A coupe of questions:

Are you using Direct Query mode or Import mode for your data?

Is there only ever one row per project ID (in Power Query), or can project ID's repeat with the sum of [Total value] being their value?

Pete

[Anonymous]

Hi Pete,

Thanks, so there is only 1 project number per value there are no duplicate project numbers. 

The date is being imported from another pbix file which hod our data set.

Thanks

Alex

[BA_Pete · Accepted solution]

@Anonymous ,

Ok, so it sounds like this can be done in Power Query on your Financial table. I presume this holds a [Project ID] field and a [Project Value] field or similar.

Based on that assumption, in Power Query select your Financial table query then go to the ribbon and select the 'Add Column' tab, and choose 'Custom Column'.

Use something like this in the 'Custom column formula' section of the dialog:

if [Project Value] < 5000000 then "0-5 Million" else
if [Project Value] < 20000000 then "5-20 Million" else
"20 Million +"

You would then use this field in the 'Legend' section of the visual set up.

Pete

[Anonymous]

Hi Pete,

That worked great. I have applied this formula to the dataset and used this in my report and it works just how i need it to in the pie chart

Thanks very much for your help

Thanks

Alex