Forum Discussion
Helper IIIHow to add one calculated column matrix column
Hello
Please see below table. I want to add one calculated coumn as %NA after 06-NA column.
In this %NA column I want use formula as %NA = 06-NA / Total. I tried following formula from another post, but %NA column is adding with each column instead of adding one time. I just want to add at the end before Total column. Let me know this doiable or not.
%N/A =
IF(
ISINSCOPE(Query1[Status]),
BLANK(),
DIVIDE(
CALCULATE( COUNT(Query1[Status]),Query1[Status]="06-NA"),
COUNT(Query1[Status])
)
)
7 Replies
- amitchandak
Super User
if you are looking for a Hybrid display with Matrix Column and measure
https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1354591
https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/963588#M428vote for Hybrid Table
https://ideas.powerbi.com/ideas/idea/?ideaid=9bc32b23-1eb1-4e74-8b34-349887b37ebc
avi081265Hello Amitchandak
Thanks for reply.
I found one post which was also having almost 90% same type of requirement. Only problem is that I can l still see blank values for each status but the total will be correctly shown. Is there any way to stop see blank values for each status. Because this solution is perfect for me.
If I exclude the column it will exclude count column also,.
I found following post
Solved: Re: How to Calculate percentage depending on dynam... - Microsoft Power BI Community
Avian
Hi avi081265 ,
After my test, the post you provided should not apply to your scenario.
It is recommended that you use the pivot function and then create a new calculated column.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- avi081265
Hello Stephen,
Is there any article or blog where I can found some detail information about create pivot table using new calculated column?
- avi081265
Hello All,
Is it possible to calculate percentage for each status and each row? See below screen chart and red color box. How can we implement this. First Image is field mapping and second image waht I am looking for
How Can I display % cerntage for each status for each row?
Thanks in advance.
Avian
Hi avi081265 ,
I created some sample data as follows
If you want to display % cerntage for each status for each row? Just put the meaesure you created into values.
Measure = DIVIDE(MAX('Table'[Value]),CALCULATE(SUM('Table'[Value]),ALLEXCEPT('Table','Table'[User])))If you only want to display percentages in one column, you need to pivot in Power Query. Select the Category column and click Pivot Column, Values column to select value, and select Don't Aggerate in the advanced option.
Create the NA% measure
NA% = DIVIDE(SUM('Table (2)'[06-NA]),SUM('Table (2)'[01-PLAN])+SUM('Table (2)'[02-DO])+SUM('Table (2)'[06-NA]))You can check more details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- avi081265
Hello Stephen,
I shared link of sample file with you as private message. Please review.
Avian
