Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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])
)
)
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.
Hello Stephen,
I shared link of sample file with you as private message. Please review.
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.
Hello Stephen,
Is there any article or blog where I can found some detail information about create pivot table using new calculated column?
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/1...
https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/...
vote for Hybrid Table
https://ideas.powerbi.com/ideas/idea/?ideaid=9bc32b23-1eb1-4e74-8b34-349887b37ebc
Hello 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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |