Issue with reversing a percentage

[BarneyR]

Hello,

I am having a really frustrating issue with some of my data.

I have a measure which looks like this:

Missing Data = IF([total_percentage] < 1, 1 -[total_percentage],0)

[total_percentage] column

total_percentage = pa_output[hourly_demonstrated_percentage] + pa_output[hourly_downtime_percentage] + pa_output[hourly_reject_percentage]

I simply want to add up all 3 percentages and if they are greater than 100%, then Mising Data should be 0. If they are less than 100% then the Missing Data should be the differnce between the sum of percentages and 100%.

The issue is that if minus 1 from the total percentage to get the difference, it seemingly adds incorrect data to my values.

This is incorrect, for this record there is no blank hour, and no hour 10, 11, 12 & 13. These values do appear in my table, but do not relate to this record.

However, if I remove the  1 - from the calculation, my data displays the correct amount of rows:

I am struggling to figure out why it is behaving this way and I cannot seem to find a way to get the same results as figure 2 but with the correct percentages (the difference between the total and 100%, instead of the total %).

Has anyone come across this before? Any help getting me in the right direction would be appreciated. If any more info needs providing please let me know.

Thanks

[Anonymous · Accepted solution]

Hi @BarneyR 

From your figure 2 ,I think the result you want for Missing Data is the sum of three percentages, 'Table'[Output%] , 'Table'[Downtime%] , 'Table'[Reject%]

Calculated column

Missing Data = IF('Table'[Output%]+'Table'[Downtime%]+'Table'[Reject%]<1,'Table'[Output%]+'Table'[Downtime%]+'Table'[Reject%],0)

If the sum of 3 percentages <1,the return value is 0 .Otherwise ,it will return the sum of 3 percentages .

The effect is as shown:

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[selimovd]

Hey @BarneyR ,

maybe you can show us the formula for [Missing Data]?

That would help us find the issue.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[BarneyR]

Hi there @selimovd ,

The formula is hourly_missed = IF([total_percentage] < 1, 1 -[total_percentage],0)

I just referred to it by the wrong name, but I will edit the post.

Thank you

[selimovd]

Hey @BarneyR ,

so the [Missing Data] is the sum of the column hourly_missed?

As long as there is a value for the rows in the hours 10-13 there will also be a result in the table.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[BarneyR]

Hi @selimovd  Missing Data is just hourly_missed but I have renamed it. There are no values for hours 10-13 so this is why I am confused why it's showing 100%.

[selimovd]

Hello @BarneyR ,

you said it's a calculated column with the following formula:

Missing Data = IF([total_percentage] < 1, 1 -[total_percentage],0)

This means the rows for hours 10-13 have either "1-[total_percentag]" or a 0 as a value.

So they must have values.

Can you post a screenshot with the calculated column for the hours 10-13 or share the file? This would make it easier.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[BarneyR]

Hello @selimovd 

Unfortunately it is not letting me give a proper reply as it is complaining about HTML being incorrect but I am not even using the HTML editor, nor is it showing me what HTML is actually incorrect.

There is no data for this record for hours 10-13. There is data in this table that do have hours 10-13, but they are for a completely different record.

It seems like it is showing me data regardless of it being the selected parent_id (which is the column that groups the relevant rows together).

I have a relationship to pa_header on pa_header.id = pa_output.parent_id.

What's odd is that if I remove the calculation that works out the remaining percentage out of 100, the correct number of rows show (9). It's when I minus 1 from the percentage (to get the remainder) when it messes up.

[selimovd]

Hey @BarneyR ,

yes, I had the issues with HTML in past also. It's very annoying.

Can you provide the file? It would make it a lot easier to help you.

Best regards

Denis

[BarneyR]

Hi @selimovd 

I will have to create a new file with test data as this currently links to SQL. But if I replicate the data inside Power BI I should be able to send you it. I will reply again once I have recreated it.