Subtract specific value from each row of a column

[Anonymous]

Hello community. My problem is the follow:

F total / y total = 4.5151.

I would like to substract the above value (4.5151) from each row of w column an create a new column from the results (The w column is the result of F / y columns). For instance

4.5151 - 3.38 = 1.1351, 4.51.51 - 4.5 = 0.0151. In excel is very easy but I can't do it on PowerBI.

Please also have in your mind that I have filter the hour column to have specific hours and also I have a sclicer to select date and month.

Can you help me please?

[harshnathani · Accepted solution]

Hi @Anonymous ,

Create a Calculated Column

Cc = 
var a = SUM('Table'[F])
var b = SUM('Table'[y])
var div = DIVIDE(a,b)
RETURN
div - 'Table'[w] 

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Fowmy]

@Anonymous 

Add these two measures:

W Column:

w = 
VAR L = DIVIDE(
    SUM([F]),
    SUM([Y])
)

VAR FTOTAL = CALCULATE(SUM([F]),ALLSELECTED('DATA'))
VAR YTOTAL = CALCULATE(SUM([Y]),ALLSELECTED('DATA'))
VAR GTOTAL = DIVIDE(FTOTAL,YTOTAL)
RETURN
IF(
    HASONEVALUE('DATA'[F]),
    L,
    DIVIDE(
        FTOTAL,
        YTOTAL
    )

New Column Measure:

New Column = 
VAR FTOTAL = CALCULATE(SUM([F]),ALLSELECTED('DATA'))
VAR YTOTAL = CALCULATE(SUM([Y]),ALLSELECTED('DATA'))
VAR GTOTAL = DIVIDE(FTOTAL,YTOTAL)
RETURN
IF(
    HASONEVALUE('DATA'[F]),
    [w] - GTOTAL
    ,
    BLANK()
)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

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

YouTube, LinkedIn

[amitchandak]

@Anonymous , Try like

calculate(divide(sum(Table[F]),Sum(Table[y])), all(Table)) - divide(sum(Table[F]),Sum(Table[y]))

or

calculate(divide(sum(Table[F]),Sum(Table[y])), allselected(Table)) - divide(sum(Table[F]),Sum(Table[y]))