Calculate Revenue Based On Fixed AND Variable Amount

Hi, let’s try the following:

• We'll write a measure to compute the revenue for each client, based on whether they are xxyyzz or another client.
• We'll need to count the number of jobs for xxyyzz for each month and adjust the revenue accordingly.

Below is a DAX measure to do above steps:

Adjusted Revenue =

VAR ClientID = SELECTEDVALUE(fact_clients[client_id])

VAR JobCount =

    CALCULATE(

        COUNTROWS(dim_jobs),

        dim_jobs[client_id] = ClientID

    )

VAR RevenueForXxyyzz =

    IF(ClientID = "be1259a7-bc71-4b30-9c42-8568874bd87b",   // xxyyzz Client ID

        IF(JobCount <= 900,

            50000,  // $50,000 if the job count is less than or equal to 900

            50000 + SUM(dim_jobs[final_revenue_total])   // $50,000 + the revenue for jobs above 900

        ),

        SUM(dim_jobs[final_revenue_total])   // For all other clients, just sum the job revenue

    )

RETURN RevenueForXxyyzz

Note: RevenueForXxyyzz: This is the key part of the measure. If the client is xxyyzz (identified by their client id), we Check if the job count is less than or equal to 900. If so, we return $50,000. If the job count is greater than 900, we return $50,000 plus the sum of the final_revenue_total for jobs above 900. For all other clients, the measure simply sums the final_revenue_total (standard revenue calculation).

• Drag the Adjusted Revenue measure into your report (e.g., a table or matrix).
• Use the Month from your dim_dates table as the rows or columns.
• Filter by Client (from the fact_clients table) to show revenue for individual clients.

Note: If you're tracking monthly revenue, you'll need to ensure that the count of jobs (JobCount) is being done per month. The DAX above assumes you're working within a time context (e.g., Month, Year) where job counts are calculated based on the current filter context. If you're summing revenue by month, the COUNTROWS will be filtered by month automatically. Make sure that your data for final_revenue_total is clean and that jobs are correctly assigned to clients and months.

If this helps, Please let me know.

Hi Ed2563 

To calculate the revenue per client per month with special handling for client xxyyzz, follow these steps:

1. Create a Calculated Column in the dim_jobs Table

Add a calculated column to assign a sequential job number to each job for client xxyyzz based on the job date:

JobNumber = 
IF(
    dim_jobs[client_id] = "be1259a7-bc71-4b30-9c42-8568874bd87b",
    RANKX(
        FILTER(
            dim_jobs,
            dim_jobs[client_id] = "be1259a7-bc71-4b30-9c42-8568874bd87b"
        ),
        dim_jobs[job_date],
        ,
        ASC,
        DENSE
    ),
    BLANK()
)

2. Create a Measure for Total Revenue

Define a measure that calculates the revenue per client per month, applying the special logic for client xxyyzz:

Total Revenue = 
VAR ClientID = SELECTEDVALUE(fact_clients[client_id])
RETURN
IF(
    ClientID = "be1259a7-bc71-4b30-9c42-8568874bd87b",
    // For client xxyyzz
    VAR RevenueForJobsAbove900 =
        CALCULATE(
            SUM(dim_jobs[final_revenue_total]),
            dim_jobs[client_id] = ClientID,
            dim_jobs[JobNumber] > 900,
            dim_jobs[job_date] >= MIN(dim_dates[date]),
            dim_jobs[job_date] <= MAX(dim_dates[date])
        )
    RETURN
        50000 + RevenueForJobsAbove900,
    // For other clients
    CALCULATE(
        SUM(dim_jobs[final_revenue_total]),
        dim_jobs[client_id] = ClientID,
        dim_jobs[job_date] >= MIN(dim_dates[date]),
        dim_jobs[job_date] <= MAX(dim_dates[date])
    )
)

Explanation:

• For client xxyyzz:
  • Adds a fixed $50,000 per month.
  • Calculates additional revenue from jobs exceeding 900 by summing final_revenue_total for jobs where JobNumber > 900 within the current month.
• For other clients:
  • Sums final_revenue_total for the current client and month.

3. Build Your Report

• Columns: Use dim_dates to display months (e.g., Jan-24, Feb-24).
• Rows: Use fact_clients[ClientName] to list clients.
• Values: Insert the Total Revenue measure.

This setup will display the revenue per client per month, accurately reflecting the fixed and variable amounts for client xxyyzz and summing the revenue normally for other clients.

Note: Ensure that your relationships between tables are correctly established and that date filters are properly applied to reflect the desired time periods in your report.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube 

Hi,

Share some sample data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.