Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Ed2563
Frequent Visitor

Calculate Revenue Based On Fixed AND Variable Amount

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:

ClientJan-24Feb-24Mar-24Apr-24May-24Jun-24Jul-24Aug-24Sep-24Oct-24YTD Total
aabbcc91,371116,48871,55684,919117,347105,06172,94680,76485,54565,608891,605
bbccdd85,05264,499116,411116,98979,68295,78557,749109,03666,430108,898900,531
ccddee83,44391,677114,76870,62567,623101,50375,05189,79792,187108,893895,567
ddeeff84,96986,89458,727117,180111,32364,510103,856110,94556,57869,508864,490
eeffgg87,312107,700111,62078,28892,87581,37158,17568,867117,89773,311877,416
xxyyzz50,00050,00050,00050,00050,00050,00050,00050,00050,00050,000500,000
Month Total482,147517,258523,082518,001518,850498,230417,777509,409468,637476,2184,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.

 

1 ACCEPTED SOLUTION
v-yajiewan-msft
Community Support
Community Support

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:

vyajiewanmsft_0-1732671552195.png

 

Best regards,

Joyce

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

View solution in original post

9 REPLIES 9
v-yajiewan-msft
Community Support
Community Support

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:

vyajiewanmsft_0-1732671552195.png

 

Best regards,

Joyce

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

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).  

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
Advocate I
Advocate I

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
Frequent Visitor

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
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hello,

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

VahidDM
Super User
Super User

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 

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.