How to Calculate percentage depending on dynamic column and total column

[Sonashish]

Hello

I created three powerBI tables depending on combined excel. Please review following sample image. I want to add calculate % (percentage) column and display % as mentioned in 3rd table i.e. % N/A. First I created table no 1, table 2 and table 3 excepr (% NA column.  How can I add this column with value in % format.

[Fowmy]

@Sonashish 

If your Table is in that layout, add the following code as a new column to Table3:

% N/A = 
DIVIDE(
    [N/A],
    SUMX(
        {[Completed],[Work in Progrss],[Not Started],[Failed],[N/A]},
        [Value]
    )
)

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[Sonashish]

Hello, It seems there is some confusion, let me clarify that there is one column called Status is containing Completed,Work in Progrss,Not Started,Failed,N/A values..

When I try to add suggesed code in new column, it throws the error

Column 'N/A' cannot be found or may not be used in this expression

Am I missing something?

Sona

[AllisonKennedy]

@Sonashish Assuming your data is one appended table with columns: Location, Date, Status.

You can use CALCULATE To modify the filter context, and get only NA and then ALL and calculate percent. 

The problem is that this cannot be combined with the table by status that you have drawn in Excel in table 3 in a pretty format, because you'd be trying to combine columns and measures. So would it be possible to use two tables for table 3 info?

% NA = DIVIDE(

CALCULATE(COUNT(Table[Date]), Table[Status] = "NA")

, CALCULATE(COUNT(Table[Date]), ALL(Table[Status]) )

)

Like I said, this won't look pretty in your table 3 matrix. Another option could be to create a % of total measure that will look a bit better in table 3: 

% Total Statuses = DIVIDE(

COUNT(Table[Date])

, CALCULATE(COUNT(Table[Date]), ALL(Table[Status]) )

)

[Fowmy]

@Sonashish 

I assumed you already have a table as per your image, the 3rd table. 
You can share the screenshot of your table or share dummy data in the same format as your table.

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn
 

[Sonashish]

Please see my original email, and see table 3 screenshot. In actual table % N/A column is not there. I created same table in excel and added % N/A,  just to explain what I am looking for.

In actual excel files there are 3 columns i.e. locations, date and status. After combining the excels, I created table 1,  table 2, table 3 as mentioned in original post. Now I want to calculate % of N/A for each row and wants to add this as column before total column. % of NA formulat should be N/A column / Total Column.

I hope this is clear.

Sona

[Fowmy]

When u say you created table 3, so You refer to physical tables or table visuals?  Check my screenshot, i created a new column for %N/A

Share the screenshot of the excel table with three columns please. 

[Sonashish]

Hello Fowmy,

You can see above table 3 screenshot. In actual table, % N/A column is not there. I copied whole table in excel and added one more column i.e. % N/A to explain what I am looking for .

Let me explain whole scenerio again. 

I combined excel files and each excel file has 3 columns location, date, and status. After combining the excel I selected these 3 selected and added in matrix reports(this was user requirement to present data in different way).  I created 3 matrix table as mentioned above. The only thing I am not able to calculate and add the % N/A  column in 3rd table. I tried so many options, I am sure I am missing some steps.

Thanks

Sona

[Fowmy · Accepted solution]

@Sonashish 

So you have created matrix visuals. You can add the following measure to your 3rd matrix but you will still see blank values for each status but the total will be correctly shown:

%N/A = 
IF( 
    ISINSCOPE(Data1[Status]),
    BLANK(),
    DIVIDE(
        CALCULATE( COUNT(Data1[Status]),Data1[Status]="N/A"),
    COUNT(Data1[Status])
    )
)

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[Sonashish]

Yes, it is working as expected. Thanks you very much.

But as you said blank value column appearing for each status. Is there any way we can hide this blank value column?

Sona

[littlemojopuppy]

This shouldn't be that difficult, assuming that your data is not laid out like shown in the first table.  Try laying out your raw data like this

Your problem is the underlying data model.

[Sonashish]

Hello,

Thanks for reply, but unfrtunately this is the user requirement so I have to worklike this 😀

Sona

[littlemojopuppy]

@Sonashish users can specify "these are the inputs" and "these are the desired outputs" but it's rare that a user knows enough to outline exactly how something should be done.  If they could, wouldn't they likely do it themselves???  In Power Query you could unpivot the date and status into a nice, clean table and this would have been simple and done yesterday.  But since you insist on not doing so, @Fowmy has already given you a solution that would work, except you need to substitute the correct field name for what he provided.

[Sonashish]

Hello All,

You guys may br providing proper solution and I am not be able to do it. If you can share me some common repository where I can upload sample files. So you can review and explain using sample BI file with covering three table format. I am newbie and still learning PowerBI, so might be I am not able to understand.

Thanks

Sona

[littlemojopuppy]

Hi @Sonashish.  Looks like @Fowmy has gotten you pretty much squared away.

I wanted to offer a recommendation for a very good Udemy course for Power BI.  I ask the newbies who work for me take it.  It's long, the instructor is at times a little annoying, but it is an excellent course.  Not only explains Power BI very well, but it does so in a workflow of how to approach a Power BI project.  It's excellent and it's on sale for $12 through today.

Good luck!

[littlemojopuppy]

Drop something into Drop Box, One Drive, Google Drive and provide a link so we can download

[Sonashish]

PLease download it from https://sonashish-my.sharepoint.com/:x:/g/personal/akanoo_sonashish_onmicrosoft_com/EdecPdvxMJFGnOrq... 

Let me know if you have any issue during download.

Sona