Forum Discussion
Calculated Column not working with measures
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.
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.
9 Replies
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.
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.
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 ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
