Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Creating a Conditional Measure

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

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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
Not applicable

Hi Nathan,

 

Thank you! This worked exactly how I wanted

 

Cheers,

 

Todd

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.