Calculated Column not working with measures

[Anonymous]

I need to create a pie chart that shows the Top 5 projects and then groups the rest under "others".   I understand how to write these as measures but I need a calculated column to use as the details in the Pie Chart.   Does anyone know a way to make this work?
Here is my calculated column:

Top 5 =
IF(
[Project Rank]<=5,
IssuesList[ProjectName],
"Others"
)

Here's an image to show what is happening to the measures.   For the pie chart I would need Top 5 as the Details and Days Worked as the Values.

Once I add the Top 5 Calculated column to the mix, Project Ranking breaks.  If I try to make Project Ranking a calculated column, then Days Work breaks inside the calculated column.

[v-lionel-msft]

Hi @Anonymous ,

Can we see the formula for measure [Project Rank]?

And please tell us whether the columns referenced in [Project Rank] are fact columns or derived from DAX calculations.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-lionel-msft]

Hi @Anonymous ,

Can we see the formula for measure [Project Rank]?

And please tell us whether the columns referenced in [Project Rank] are fact columns or derived from DAX calculations.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MFelix]

Hi @Anonymous ,

You cannot use measures on a calculated column try the following:

• Create a table with the following format

DescriptionRank

P1	1
P2	2
P3	3
P4	4
P5	5
Others	6

• Then use the following code for a measure to place in pie chart:

Pie Chart = SWITCH(TRUE(); SELECTEDVALUE('Ranking'[Rank]) = 6 ;CALCULATE([Days_Mea];FILTER('Table';[Rank] >= SELECTEDVALUE( 'Ranking'[Rank])));CALCULATE([Days_Mea];FILTER('Table';[Rank] = SELECTEDVALUE( 'Ranking'[Rank]))))

Now create your chart with measure created above in the values and Description of the table created in the legend.

Check result in PBIX attach.

[Anonymous]

Thanks - unfortunately I would not be able to use alias P1, P2, etc in the visual it would need to be the Project Name.

[MFelix]

Hi @Anonymous ,

Do you need this visual to be interactive with other selections? Like dates, users, whatever?

The calculated column is doable however is not dinamic, you can also use a summarized table, once again depending on the filters you need the interaction with other visual is limited.

[Anonymous]

We filter by date period, Team, Product, Release and Project so there is alot going.  It doesn't seem like this will be possible to do.

[MFelix · Accepted solution]

Hi @Anonymous ,

Using the blog post below I was abble to achieve necessary value:

https://www.proserveit.com/blog/ms-power-bi-topn-and-other

Only change was that I created the Top5 and others on the query editor since it was giving me a circularity from dax.

You can also make this even more dinamic if you place the Top N as a what if parameter.

Check PBIX file attach.

[Anonymous]

This worked perfectly with all the slicers too - Thanks.

[MFelix]

Hi @Anonymous ,

Glad I could help has refered you can make the TOP N a dinamic value don't know if for now is needed but keep it in mind.