The data in my report is somehow different than it is in Power Query?

[P8MCP]

Hi Guys,

Forgive me if I'm being stupid, but never before have I came across this issue with Power BI and I can't figure out what the problem is.

So, I have created 3 conditional columns (Severity Number, Impact Number and RAG Score) in Power Query as per below

Then when I close and load the data, these are the results I'm getting in my tables and visuals:

No further calculations were done, I have cleared my cache and refreshed everything in both Power Query and in the report view and still, these numbers are somehow changing from 5, 5 and 25, to 15, 15 and 75. Anyone know what I am doing wrong as I am baffled?

Thanks in advance!

[alekhved]

hi @P8MCP 

Can we know what's the calculation used in conditional columns?

Thanks!

[P8MCP]

hi @alekhved 

Sure of course. So the data I have for Severity and Impact is structured like this in the source "1 - Very Low", "2 - Low", "3 - Medium" etc.

So for both the "Severity Number" and "Impact Number" columns, I have extracted the numbers to do my calculations as per below:

Then the RAG Score is a simple multiplication of both of these columns together. In Power Query my results looks perfect, with nothing scoring over 25 (as it should be) which can be seen below:

These are the last steps in my Power Query so nothing else has been done after this, yet my results are still different when loaded 😞 

[alekhved · Accepted solution]

hi @P8MCP ,

I guess you have 3 records (possibly duplicate) for NSP-840 in your source file. Hence after load, the numbers are getting aggregated. If that's not the case, I would try to define the Severtiry number as Text (as the output format in calculation) and later convert to number if required.

Best Luck!

Regards!

[P8MCP]

Hi @alekhved

Accepting this as the solution as defining the Severity and Impact as Text done the trick. I've been developing on Power BI for 3 years and never knew this could happen, nor do I understand why 🙃 but hey ho. Thanks so much you've really helped me out for going into the new working week!