cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Using one row as variable for calculations

This will require some explaining.

Let's say we have 4 clients to which we book time against their code.
We also have internal code agasint which internal work is booked.
We want to divvy up the internal work amongst the 4 clients, apportioned to their weighting, i.e. the proportion of time booked against them.
Client Jan Feb March

A        $5  $1   $3

B        $1  $2   $1

C        $3  $1   $4

C        $1  $2   $3

Int     $10 $12   $4

So, I want 

JAN (Slider)

Client Mth$   Delivery Costs

A        $5       $2.5

B        $1       $2

C        $3       $1.5

C        $1       $2

Feb(Slider)

Client Mth$   Delivery Costs

A        $1       $2

B        $2       $4

C        $1       $2

C        $3       $4

So the "Delivery costs" is something like "Internal costs*client $ / total$
But I can't get anything to work!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User II
Super User II

So I think your Jan example has an error in it as the total delivery cost only adds up to 8 when I think it should be 10.

 

But otherwise if I understand your requirements correctly I think the following calc should work for you

Delivery Costs = 
VAR _intAmt = CALCULATE(sum('Table'[Value]), 'Table'[Client] = "Int")
VAR _totalAmt = CALCULATE(SUM('Table'[Value]) , ALL('Table'[Client]) , 'Table'[Client] <> "Int")
VAR _clientAmt = CALCULATE(SUM('Table'[Value]) , KEEPFILTERS('Table'[Client] <> "Int"))
VAR _proportion = DIVIDE(_clientAmt , _totalAmt)
return (_proportion  * _intAmt)

 

The only slightly advanced bit in the above is that I've used the KEEPFILTERS() function when calculating the clientAmt (which is the total excluding the internal costs). In this way when you use the measure when the client column is on the rows it will get correctly filtered for the current client.

View solution in original post

2 REPLIES 2
Super User II
Super User II

So I think your Jan example has an error in it as the total delivery cost only adds up to 8 when I think it should be 10.

 

But otherwise if I understand your requirements correctly I think the following calc should work for you

Delivery Costs = 
VAR _intAmt = CALCULATE(sum('Table'[Value]), 'Table'[Client] = "Int")
VAR _totalAmt = CALCULATE(SUM('Table'[Value]) , ALL('Table'[Client]) , 'Table'[Client] <> "Int")
VAR _clientAmt = CALCULATE(SUM('Table'[Value]) , KEEPFILTERS('Table'[Client] <> "Int"))
VAR _proportion = DIVIDE(_clientAmt , _totalAmt)
return (_proportion  * _intAmt)

 

The only slightly advanced bit in the above is that I've used the KEEPFILTERS() function when calculating the clientAmt (which is the total excluding the internal costs). In this way when you use the measure when the client column is on the rows it will get correctly filtered for the current client.

View solution in original post

Anonymous
Not applicable

Absolutely Brilliant!
I literally plugged in with my field names and it worked no errors first time! 

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors