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

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

Reply
abloor
Helper IV
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.

 

How would you go about this?

 

Thanks in advance.

 

FruitSales.png

1 ACCEPTED SOLUTION
TomMartens
Super User
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)

image.png

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

View solution in original post

4 REPLIES 4
TomMartens
Super User
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)

image.png

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

Thanks @TomMartens  this works perfectly.  You rock!

 

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

🙂 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

@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
)
)

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.