Referencing another query in custom column

[Anonymous]

Hi - I have a query that has one value in it.  I want to take that value, and divide it by the number of rows in another query. Then, subtract that value from all the rows in that second query.  I created a measure that taskes the single value and divides it by the row count, and that worked.  But I can't figure out how to reference the unique value produced by that measure and reduce every row by that value.  

E.g. I have a query (Query1) that I filtered down to just be 1 row, 1 column.  The value of that cell is $639,792.  On the visual screen, I did a quick measure that divided the $639,792 by the total number of rows (which for me is a count of how many weeks) in another query (Query2).  The value came out to $4300.  I now want to take that $4300 and subtract it from every row in Query2 in a custom column. For the sake of time, I have done a manual "subtract" of the $4300 to work for my presentation today, but I'd like it to be automated.  

[Anonymous · Accepted solution]

Hi @Anonymous ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a calculated column on Table Monthly RMP Forecast.

Column =
[RMP]
    - DIVIDE (
        MIN ( 'Anticipated PTO'[Anticipated PTO $ Value Total] ),
        COUNTROWS ( ALL ( 'Monthly RMP Forecast' ) ),
        0
    )

(3) Then the result is as follows.

Best Regards,

Neeko Tang

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

[Anonymous]

That worked! Thank you.  If we wanted to count rows based on the week ending column and only count rows that are after TODAY(), how would we do that?

[Anonymous]

I want to take column "RMP" in Query "Monthly RMP Forecast" and minus "Anticipated PTO" (from another query)/Table.RowCount (from "Monthly RMP Forecast")

[Anonymous]

It would be even better if I could just subtract is from the rows whose date is in the future.