Use a static measure output as another measure's input

[Anonymous]

Hi all,

I would like to make a calculation that uses another measure's result as the input for my table.

Say I have a Sales Quantity, Hours Worked, Total Costs and Departments.

I have a measure that calculates the CostPrice/Hour with Hours Worked and Total Costs. Let's say it shows $50 for 2020.

Now I want to calculate the Cost by Department. I multiply Hours Worked per Department by the CostPrice/Hour, but since CostPrice/Hour was calculated based on the Hours Worked in the first place, the Cost per Department now shows the Total Costs for the entire company.

Is there any way to work around this? I basically need the $50 as a static measure only affected by the Date filter.

Thanks for any help!

[Anonymous · Accepted solution]

I found the solution in another thread: 

https://community.powerbi.com/t5/Desktop/Ignoring-axis-context-for-a-measure-in-chart/td-p/704443

Using ALLSELECTED(department table)  in my formula:

DprtCost = CALCULATE(SUM(quantity) * CALCULATE( [TotalCosts] / SUM(quantity), ALLSELECTED(departments)

Thanks for the help everyone!

[Anonymous]

Hi @Anonymous ,

I have reproduced some data shown below:

Let's say:

2020 -->[CostPrice/Hour]=50, Department A Hour=25 

2021 --> [CostPrice/Hour]=47, Department A Hour=17

Your expected output is:

when select Year=2020 in slicer, you want the CostPrice/Hour=50 not (50+47)/2  to calculate (25+17) *50 for Department A, right?

If so,Please use the following formula to calculate CostPrice/Hour and Hour *CostPrice/Hour of each Department:

CostPrice/Hour =
CALCULATE (
    SUM ( 'Table'[Total Costs] ) / SUM ( 'Table'[Hours Worked] ),
    ALLEXCEPT ( 'Table', 'Table'[Year] )
)

Measure =
CALCULATE (
    SUM ( 'Table'[Hours Worked] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Departments] = MAX ( 'Table'[Departments] ) )
) * [CostPrice/Hour]

You could check the pbix file here.

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

[Anonymous]

Hi Eyelyn, 

thanks for your reply. I'm afraid I still don't quite understand your calculations here.

I have a measure for my TotalCost/Hour that divides Total Costs by HoursWorked (stored in separate tables) which gives the correct result as a measure by itself, which I can see in a visual displaying the TotCost/Hour measure.

Then I have a bar chart with departments on the axis. When I calculate the department cost/hour, the bars each calculate their own TotalCost/Hour, which results in all of them being the same value, since TotalCost/Hour is calculated by dividing by HoursWorked.

Basically, all I need is for that number in the TotCost/Hour visual to not change when applying them to my bar chart. Is there a way to do this?

Thanks so much for your time!

[amitchandak]

@Anonymous ,

Typically

Measure =

var _1 = calculate([CostPrice/Hour], all(Table) ) //Should give us contant value in text of the meausre .

But need to check with data and need

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.