Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
avi081265
Helper III
Helper III

How 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.

tempsnip.jpg

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])
    )
)

 

 
Thanks
Avian

 

 

7 REPLIES 7
avi081265
Helper III
Helper III

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 

status.PNG

 

tempsnip.jpg

 

How Can I display % cerntage for each status for each row?

 

Thanks in advance.

Avian

 

 

Hi @avi081265 ,

 

I created some sample data as follows

23.png

 

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])))

22.png

 

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.

24.png

25.png

 

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]))

26.png

 

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

v-stephen-msft
Community Support
Community Support

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?

 

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

  

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.