Creating a Conditional Measure

[Anonymous]

Hey Guys,

I have multiple departments which all have revenue but one of those departments needs to be split up and allocated to 2 other departments (ex. Dept 1, Dept 2, Dept 3 and Dept 3 needs to be split into 2 and added to Dept 1 and Dept2)

I want to create a measure that will take revenue from Dept 1 and add Dept3 * 0.2 and take revenue from Dept2 and add Dept3 * 0.8 and set Dept3 to 0 so that I can exclude it

Any help or insight would be great.

Thank You

[Anonymous · Accepted solution]

Hi @Anonymous ,

If this is always the allocation, and you don't want dept 3, you can build that logic into your ETL / Power Query. But if you just want to do this in certain situations, you could do a measure like this:

Sales Amount Total Redistribution =

var distfromUnfilteredAmount = CALCULATE([Sales Amount Total], FILTER(ALL('Product'[Product Name]),'Product'[Product Name] = "Large Backpack"))

var distfromCount = COUNTROWS(FILTER('Product','Product'[Product Name] = "Large Backpack"))

var dist1Count = COUNTROWS(FILTER('Product','Product'[Product Name] = "Medium Backpack"))

var dist2Count = COUNTROWS(FILTER('Product','Product'[Product Name] = "Small Backpack"))

var distfromAmount = IF(distfromcount > 0, -1*distfromUnfilteredAmount,0)

var dist1Amount = IF(dist1count > 0, .8*distfromUnfilteredAmount,0)

var dist2Amount = IF(dist2count > 0, .2*distfromUnfilteredAmount,0)

return [Sales Amount Total] + distfromAmount + dist1Amount + dist2Amount

Notes:

1. This works properly, unless there is no value for the department that is being distributed to. It will show zero for those.

2. You'll need to substitute out [Sales Amount Total] with your measure and the product references with your table/attribute/value.

Hope this helps,

Nathan

[Anonymous]

Hi Nathan,

Thank you! This worked exactly how I wanted

Cheers,

Todd