cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Helper IV

## Create a measure which includes a calculation for a filtered item

Hi all,

I'd like to create a Measure that uses a filter which multiplies the gross cost for Bananas by 0.33, but keeps Apples and oranges at their full gross cost.

e.g. I grow the Apples and Oranges myself so keep 100% of the revenue, however my friend grows the bananas and I only get to keep 33% of the sale price.

1 ACCEPTED SOLUTION
Super User

Hey,

using this measure

```Gross Cost Adjusted =
SUMX(
VALUES(Table1[Fruit])
,
var thisProduct = 'Table1'[Fruit]
var thisFactor = IF(thisProduct = "Banana" , 0.33 , 1 )
return
CALCULATE(SUM(Table1[Gross Cost]) * thisFactor
)
)```

allows me to create this table visual (and of course the measure will work with all the other visuals)

Hopefully this is what you are looking for.

Regards,
Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
4 REPLIES 4
Super User

Hey,

using this measure

```Gross Cost Adjusted =
SUMX(
VALUES(Table1[Fruit])
,
var thisProduct = 'Table1'[Fruit]
var thisFactor = IF(thisProduct = "Banana" , 0.33 , 1 )
return
CALCULATE(SUM(Table1[Gross Cost]) * thisFactor
)
)```

allows me to create this table visual (and of course the measure will work with all the other visuals)

Hopefully this is what you are looking for.

Regards,
Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Helper IV

Thanks @TomMartens  this works perfectly.  You rock!

Now I better go get selling some more Apples, Oranges and Bananas...

Super User

🙂 an apple a day, ...

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Helper IV

I would like to change my it now and have a different percentage based on a date.

e.g. My share of revenue was 33%, as demonstrated in the below measure.  But as of 1 July 2019, I will now only get 25% for the bananas.  How would I add in a time based line which calculates 33% pre-July, and 25% post July?

My column for the date is called MONTH.

Thank you!

Gross (Banana Net) = SUMX(
VALUES('Table1'[Fruit])
,
var thisProduct = 'Table1'[Fruit]
var thisFactor = IF(thisProduct = "Banana" , 0.33 , 1 )
return
CALCULATE(SUM('Table1'[GrossRevenue]) * thisFactor
)
)