Substract value from each row of a column

[Anonymous]

Hello guys. I am newbie on Power BI tool so I have a question. I have this data as an example on Excel.

I divide  Z / W = 4.52 . This is the easy step.

I want to substract the 4.52 (Z/W) from each row from column X and create new column with the results. For example 4.52-3.38 = 1.14. Keep in mind that I have some slicers that affect my report. Thank you.

[amitchandak]

@Anonymous , as a new column

table[X]- divide(sum(Table[z]),sum(Table[w]))

[Anonymous]

Thank you @amitchandak for your answer but it's not the result that I want (it's my fault). Check the image below.

What I want is to take the Total of Ratio (Actual) column which is 4.52 and then substract it from each row of the same column. For example 4.52 - 3.38 (the first row of this table), 4.52-4.5(2nd row) etc. and put the results in Column 2. However I have slicer that affect the report like the above one and some other like date. Thank you again.

[amitchandak]

@Anonymous , I was expecting a need of measure and  I given a column

try like

[X]- calculate(divide(sum(Table[z]),sum(Table[w])),allselected())

[X]- calculate(divide(sum(Table[z]),sum(Table[w])),all(table))

Here [X] is also meausre with aggreation you alreay have 

[Anonymous]

Hi @amitchandak  again and thank you. Please can you replace the columns of calculation that you've done with the real columns name that I have from PowerBI screenshot? Also which of two calculations that you write must use? I am sorry but as I said before I am newbie. Please keep in mind that this visualizatio table is from  two database tables that I have made relationships. Thank you again.

[dax]

Hi @Anonymous ,

I don't know your expression used on "Total of Ratio (Actual)", so if possible, could you please inform me more detailed information(such as your expected output and your sample data )? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

[Anonymous]

Hi @dax . Thank you for your help. Please find below the data and the table that I am using.

Those data is from 2 tables. Lets say Table A and Table B. The column Total Ratio(Actual) is a measure expression:

Ratio (Actual) =

DIVIDE(

    SUM('TableA'[z]),

    SUM('TableB'[Actual])

)

So I have the Total Ratio Actual which is 4.52. I want this number to subtract it from every row of the same column for each hour.

For example Total Ratio Actual - Ratio(Actual) [hour 10: 4.52 - 3.38 = 1.1, hour 11: 4.52 - 4.5 = 0, etc....] for every hour( 10-20) and create a new column with those results. Also I have a slicer for every date and some IDs. I will waiting for your answer. If is not clear what I want please tell me. Thanks again.

[dax]

Hi @Anonymous , 

You could try measure like below and refer to my sample for details.

Measure 2 = DIVIDE(SUM(TableA[actual]),SUM(TableB[Z]))- DIVIDE(SUMX(ALLSELECTED(TableA),TableA[actual]),SUMX(ALLSELECTED(TableB),TableB[Z]))

Best Regards,
Zoe Zhi

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

[Anonymous]

Hi @dax . I did your calculation but it breaks the table. Do you know why? Please see the image below.

[dax · Accepted solution]

Hi @Anonymous , 

You could try below sample.

Best Regards,
Zoe Zhi

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

[Anonymous]

Hi @dax . Thank you. My viz is ok now.....Thanks a lot!

[dax]

Hi @Anonymous , 

It might be caused by your data relstionship or slicer, if possible, could you please inform me your sample data(you could use virtual data instead of real data, two tables)? then I will test this in my environment.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

[Anonymous]

Hi @dax . I sent you a private message.Thanks

[az38]

Hi @Anonymous 

Try a measure like

Measure = 
DIVIDE(
CALCULATE(SUM(Table[x]), ALL(Table)),
CALCULATE(SUM(Table[y]), ALL(Table))
) - MAX(Table[x])