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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Syndicate_Admin
Administrator
Administrator

Generate a filtered Measure from two Measures and/or specific condition

Hello

I am wanting to generate a measure derived from another, based on Total Sales (previous measure) but now another, associated with products with Profit Margin (another measure) greater than 35%, and where sales do not correspond to products with this condition, which places $ 0. What dictates my understanding is that it should be Measure = CALCULATE([Total Sales], FILTER(Measures,Measures[Profit Margin]>=0.35))), however, under this formula Total Sales are being returned without being affected by the filter. Could you help me with the error or guide me with a suitable solution? The idea with this measure is to generate a column chart where total sales are displayed, how much corresponds to the number of products with the highest margin.

RAFAJRO_0-1680177527057.png

Thank you for the support and guidance.

1 REPLY 1
johnt75
Super User
Super User

You need to create a summary table which can be used as a filter, but you first need to determine at what granularity you want to measure the profit margin. If it is over the entire life of the product you could do something like 

Sales High Margin =
CALCULATE (
    [Total Sales],
    FILTER ( VALUES ( 'Sales'[Product ID] ), [Profit Margin] >= 0.35 )
)

but if you wanted to calculate profit margin at the product and year grain it would be something like

Sales High Margin =
CALCULATE (
    [Total Sales],
    FILTER (
        SUMMARIZE ( 'Sales', 'Sales'[Product ID], 'Date'[Year] ),
        [Profit Margin] >= 0.35
    )
)

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors