Calculate Revenue Based On Fixed AND Variable Amount

[Ed2563]

If this type of question has been posted previously, I haven't been able to find it.  I am also new to Power BI and have read Microsoft's documentation on DAX, as well as experimented with many type of formulas (both measures and columns). 

I have three tables (and I didn't name them):
1. Clients (fact_clients)
2. Jobs (dim_jobs)
3. Dates (dim_dates)

The relationships are set up like this (I didn't name them or create the relationships):
dim_dates       dim_jobs
date       1:*     job_date
fact_clients     dim_jobs
client_id  1:*   client_id

Within the Jobs table, there is a column that calculates revenue for the job (final_revenue_total).

We have one Client (xxyyzz) with a client_id of 'be1259a7-bc71-4b30-9c42-8568874bd87b' that pays us a set amount per month ($50,000) for a set number of jobs (900).  That client also has a per job calculated revenue amount in the jobs table just like any other Client.

Our normal reporting has been just using the calculated revenue from the jobs table for all Clients, but I have been tasked with adjusting the revenue for Client xxyyzz to accurately reflect what we are actually being paid.

Utimately, I am looking to build something like this:

Client	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	YTD Total
aabbcc	91,371	116,488	71,556	84,919	117,347	105,061	72,946	80,764	85,545	65,608	891,605
bbccdd	85,052	64,499	116,411	116,989	79,682	95,785	57,749	109,036	66,430	108,898	900,531
ccddee	83,443	91,677	114,768	70,625	67,623	101,503	75,051	89,797	92,187	108,893	895,567
ddeeff	84,969	86,894	58,727	117,180	111,323	64,510	103,856	110,945	56,578	69,508	864,490
eeffgg	87,312	107,700	111,620	78,288	92,875	81,371	58,175	68,867	117,897	73,311	877,416
xxyyzz	50,000	50,000	50,000	50,000	50,000	50,000	50,000	50,000	50,000	50,000	500,000
Month Total	482,147	517,258	523,082	518,001	518,850	498,230	417,777	509,409	468,637	476,218	4,929,609

For all Clients listed above, other than for xxyyzz, the returned value should just be a sum of the final_revenue_total.
For Client xxyyzz, the total should be the $50,000 for the first 900 jobs.  If the job count is higher than 900, then it would be:
50,000 + the final_revenue_total for any job above the included 900 jobs.

Does my explanation make sense? 

Any guidance would be appreciated and a solution even more so.

 

[v-yajiewan-msft · Accepted solution]

Hi @Ed2563 , hello all, thank you for your prompt reply!

Based on the total value issue, please try as following:

Adjusted Revenue = 

VAR ClientID = SELECTEDVALUE(fact_clients[client_id])

VAR JobCount =

    CALCULATE(

        COUNTROWS(dim_jobs),

        dim_jobs[client_id] = ClientID

    )

VAR test=IF(ClientID = "be1259a7-bc71-4b30-9c42-8568874bd87b"&&JobCount>0,   // 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]) ),
            
            SUM(dim_jobs[final_revenue_total]))  // $50,000 + the revenue for jobs above 900


RETURN test

TotalRevenue = SUMX(VALUES('fact_clients'[client_name]),[Adjusted Revenue])

SumofTotalFinal = SUMX(VALUES(dim_dates[Date].[Month]),[TotalRevenue])

TotalValue = 
IF(
    ISINSCOPE(dim_dates[Date].[Month]), 
    SUMX(VALUES(fact_clients[client_name]), [Adjusted Revenue]),
    IF(
        ISINSCOPE(fact_clients[client_name]), 
        SUMX(VALUES(dim_dates[Date].[Month]), [Adjusted Revenue]),
        [SumofTotalFinal]
    )
    
)

 Result for your reference:

Best regards,

Joyce

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

[Ed2563]

Sorry for the late response.  I got tied up with other tasks.

I have implemented this solution and it works as expected except for the column subtotals.  Even your example shows 250,000 as the column subtotal, despite the fact that there are only 4 columns with data for Client xxyyzz (4 * 50,000=200,000).  

[v-yajiewan-msft]

Hi @Ed2563,

Sorry for the missing month value for the date column in the DAX. I have updated the answer and pbix file above. Please check it.

[HCA]

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.

[Ed2563]

Thank you!  This works for calculating the month over month value correctly, but doesn't seem to work for the column or row totals.

I did make a slight modification.  For the line below, I added a check to see if there were any jobs for the client:

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

[Ashish_Mathur]

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.

[Ed2563]

Hello,

I shared as much information as I can in the original post.

[VahidDM]

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 

[Ed2563]

Thank you!  I tried this solution first, but DAX complained about these lines:

            dim_jobs[client_id] = ClientID,
            dim_jobs[JobNumber] > 900,

The client_id and JobNumber resulted in red squiggly lines.