DAX command to multiply by 100% a.k.a. x is what % of y

[desspirova]

Hi,

I'm quite new to Power BI and what I'm trying to do is a report on Power BI desktop tracking expenses vs budget. For one of my tables, I want to show a columnt with the current spent (I've done that), a column with the budget (done that too) and another column to show the % of the budget that has been consumed already. So basically x (current spent) is what % of y (budget). I tried the below but it didn't work

Consumed = CALCULATE(DIVIDE(SUM('Data23n24'[Current spent]), SUM('BugetFY24'[Total FY24 estimate]))*100%)

Any suggestions how to fix the syntax? 

Thanks in advance!

[Anonymous · Accepted solution]

Hi @desspirova ,

Thanks @DAX_Machine  for sharing, here are some suggestions, I hope they can be helpful to you.
Based on your description, I created two tables.

1. If you want to calculate the percentage of each row's cost to the total budget, you can write an expression.

Column =
CALCULATE(DIVIDE(SUM('Table'[current spent]),SUM('Table 2'[budget])))

And select "%" in column tools to format the result as a percentage. The number 2 means keeping two decimal places. You can adjust it according to your needs.

2. If you want to calculate the total cost as a percentage of the total budget, you can write an expression.

Column 2 =
DIVIDE(SUM('Table'[current spent]),SUM('Table 2'[budget]))

It also needs to be changed to percentage format.

The final result is shown below.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[DAX_Machine]

Hi!
Why not try to do it as a measure instead of a calculated column?
For that You will first need to sum your currentspent as a seperate measure, then sum your Budget as another measure, then create this one. Or if you prefer to have all in one measure you can work with variables.

This measure will calculate the diffrence between current spent and budget in %
DIVIDE([yourCurrentspentmeasure],
           [YourBudgetMeasure]
         ) - 1
Then you can format this measure as a percentage to display your value as a percent.

Try this and let me know if that helped!