DAX Calculation

[KristinaLeo]

Hi Everyone,

I require a measure that calculates for each customer tier the percentage of total revenue contributed by only products that were active for the entire duration of the current time period selection and were purchased by customers who signed up before that period began.

My tables:

-Sales (Date, ProductID, CustomerID, Units, Revenue)

-Products (ProductID, Category, LaunchDate, DiscontinuedDate)

-Customers (CustomerID, Tier, Region, SignupDate)

Additionally this calculation must:

-Exclude products discontinued during the selected period

-Consider only customers who were active before period start

-Scale the revenue by customer tier weight (Tier A=1.2, Tier B=1.0, Tier C=0.8)

-Work with dynamic period selections (month, quarter, year)

Thanks in Advance

[Ahmed-Elfeel · Accepted solution]

Hi @KristinaLeo,

I hope you are doing well ☺️❤️

Try this it should work:

Tier Weighted Stable Product Contribution % =
VAR SelectedPeriodDates = ALLSELECTED('Date'[Date])
VAR PeriodStart = MINX(SelectedPeriodDates, [Date])
VAR PeriodEnd = MAXX(SelectedPeriodDates, [Date])

-- Products active the entire selected period
VAR StableProducts =
    FILTER(
        ALL(Products),
        Products[LaunchDate] <= PeriodStart &&
        (ISBLANK(Products[DiscontinuedDate]) || Products[DiscontinuedDate] > PeriodEnd)
    )

-- Convert to single column table of ProductIDs for TREATAS
VAR StableProductIDs =
    SELECTCOLUMNS(StableProducts, "ProductID", Products[ProductID])

-- Customers who signed up before period start
VAR EstablishedCustomers =
    FILTER(
        ALL(Customers),
        Customers[SignupDate] < PeriodStart
    )

-- Convert to single column table of CustomerIDs for TREATAS
VAR EstablishedCustomerIDs =
    SELECTCOLUMNS(EstablishedCustomers, "CustomerID", Customers[CustomerID])

-- Weighted revenue from transactions that match both lists
VAR WeightedQualifiedRevenue =
    CALCULATE(
        SUMX(
            Sales,
            Sales[Revenue] *
            SWITCH(
                RELATED(Customers[Tier]),
                "A", 1.2,
                "B", 1.0,
                "C", 0.8,
                1.0
            )
        ),
        -- Apply filters using TREATAS for reliable filtering
        TREATAS(StableProductIDs, Sales[ProductID]),
        TREATAS(EstablishedCustomerIDs, Sales[CustomerID])
    )

-- Total revenue for the same date context (keep Date filters and remove other filters)
VAR TotalPeriodRevenue =
    CALCULATE(
        SUM(Sales[Revenue]),
        ALL(Products),
        ALL(Customers),
        ALL(Sales[ProductID]),
        ALL(Sales[CustomerID])
        -- Date filters are preserved automatically
    )

RETURN
    DIVIDE(
        WeightedQualifiedRevenue,
        TotalPeriodRevenue,
        BLANK()
    )

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.

[KristinaLeo]

Thanks Ahmed for this measure it is a bit longer, but this version is the best for production because its maintainable and works correctly with dynamic date selections; Exactly what I was looking for.

Additional  if I place this measure in a Matrix visual with Customers[Tier] on rows, will the Total row automatically sum to 100% across all tiers or do I need to adjust the measure for proper total calculation?

[amitchandak]

@KristinaLeo , you can try a measure like 

Measure =
Var _min = minx(allselected('Date'), 'Date'[date])
Var _max = maxx(allselected('Date'), 'Date'[date])
var _product = Summarize( filter(Products, Products[DiscontinuedDate] <= _max), Products[ProductID])
var _customer = Summarize( filter(Customers, Customers[SignupDate] < _min), Customers[CustomerID])
Return
calculate([Your Measure], Filter(Products, Products[ProductID] in _product), filter(Customers,Customers[CustomerID] in _customer))

[KristinaLeo]

Thank you for your response, but this measure will not give correct results in all cases especially on larger datasets or for products that were discontinued during the selected period, Thanks again for your response 

[FBergamaschi]

Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Alternatively, you can share your .pbix via some cloud service and paste the link here. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

Need help uploading data? click here

Want faster answers? click here