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.