Formula between 2 columns

romovaro · ‎07-22-2022

Happy Weekend

Quick question. I have the USD column per month and I created a colum called "Risk". Now I want to create a formula

Risk% = (DIVIDE('Weekly Slippage'[Risk], 'Weekly Slippage'[USD],0))

but is not really working. I get diff & but not the one I need.

For example...July should be 31.7%

I just want to have the % between the total foracsted x month vs the USD in risk.

romovaro · ‎07-26-2022

HI

Instead of Weekly Slippage USD...i created a measure called FY23 to calculate only the USD during Fiscal year.

FY23 Starts = CALCULATE(SUM('Weekly Slippage'[USD]), DATESBETWEEN('Weekly Slippage'[Ops Forecast & Actuals], DATE(2022,7,1), DATE (2023,06,01)))

I am trying to add the same "FY23 filter" to the below Risk formula but i need help.

Risk = CALCULATE(SUM('Weekly Slippage'[USD]),DATESBETWEEN('Weekly Slippage'[Ops Forecast & Actuals], DATE(2022,7,1), DATE (2023,06,01), FILTER ('Weekly Slippage', 'Weekly Slippage'[Project Health] IN {"Red" ,"Orange"}))

Thanks

Anonymous · ‎07-25-2022

Hi @romovaro ,

According to the picture shows:

Your [Risk%] shows a calculated column, can you express it with Measure:

Measure =
DIVIDE(
    [Risk],MAX('Weekly Slippage'[USD]))

Result：

Excuse me, are there any other columns in another table besides the Weekly Slippage table.

If I have misunderstood your meaning, please provide your pbix without privacy information and desired output.

Best Regards,

Liu Yang

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

romovaro · ‎07-26-2022

HI Liu Yang

I only use one table. no other tables included in the formulas

Tried your measure but still not working...weird... provides a diff % but that's not the one I need.

USD column provides USD for all the years (FY23, FY24, FY25...) i just need to use from July 2022 to June 2023.

parry2k · ‎07-22-2022

@romovaro can you put Risk % column in the visual? Are these measures or columns?

✨ Follow us on LinkedIn and to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

romovaro · ‎07-24-2022

Hi Parry2k

Currently 2 measures were created:

Risk = CALCULATE(SUM('Weekly Slippage'[USD]),FILTER ('Weekly Slippage', 'Weekly Slippage'[Project Health] IN {"Red" ,"Orange"}))

Risk% = (DIVIDE('Weekly Slippage'[Risk], 'Weekly Slippage'[USD],0))

For Risk % i have a column:

And that's the table:

What I am trying to achieve is to divide TOTAL AMOUN OF USD (all projects) vs the USD amount for the projects with Project health Red and Orange.