Forum Discussion

GalrioSamoel's avatar
GalrioSamoel
New Member
9 months ago
Solved

Need a Help in Dax Measure

I have a Sales Fact table and a Products dimension table. The requirement is to calculate Total Sales Amount only for products that are currently active. We have an “Active Flag” column in the Product table which is either 1 or 0. Active = 1 means the product is currently active, and Active = 0 means discontinued.

The problem is that the report must show historical values correctly. So if a product was sold 2 years ago, the sales amount from that period should still be included, even if the product is discontinued now. But the total value should only add amounts from products which are active according to the filter applied on Product[ActiveFlag].

In simple words:

• Use only products where ActiveFlag = 1
• Respect report filters (category, region, date, etc.)
• Sales for inactive products should be ignored completely

I tried using a filter on the visual, but it removes historical sales even though they should still be counted when ActiveFlag = 1 in the filter context. How do I write a measure that sums Sales Amount only when the product is active but still respects all slicer filters?

  • Hi GalrioSamoel,

    I hope you are doing well today ☺️❤️

     

    You can create the following measure that applies the Active filter correctly:

    Total Sales for Active Products =
    CALCULATE (
        SUM ( Sales[SalesAmount] ),
        KEEPFILTERS ( Products[ActiveFlag] = 1 )
    )

    This measure will correctly count historical sales for active products and exclude discontinued ones from totals...Let me know if it works ☺️❤️

    if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly

2 Replies

  • GalrioSamoel ,

    try this

    Total Active Product Sales =
    VAR ActiveProducts =
        CALCULATETABLE (
            VALUES ( Product[ProductID] ),
            Product[ActiveFlag] = 1
        )
    RETURN
        CALCULATE (
            SUM ( Sales[SalesAmount] ),
            Sales[ProductID] IN ActiveProducts
        )

     

     

  • Hi GalrioSamoel,

    I hope you are doing well today ☺️❤️

     

    You can create the following measure that applies the Active filter correctly:

    Total Sales for Active Products =
    CALCULATE (
        SUM ( Sales[SalesAmount] ),
        KEEPFILTERS ( Products[ActiveFlag] = 1 )
    )

    This measure will correctly count historical sales for active products and exclude discontinued ones from totals...Let me know if it works ☺️❤️

    if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly