Create a measure which includes a calculation for a filtered item

[abloor]

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.

How would you go about this?

Thanks in advance.

[TomMartens · Accepted solution]

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

[abloor]

Thanks @TomMartens  this works perfectly.  You rock!

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

[TomMartens]

🙂 an apple a day, ...

[abloor]

@TomMartens  or if anyone else is listening... Can you please help extend this measure for me?

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

)

)