Creating a calculated column to sum values based on a condition

[Tomburton2023]

Hi

I am relatively new to PowerBI and struggling to get the solution I need. Below is a screenshot of the table I'm working with, and the calculated column called 'AnticipatedWIPFeePerMatter' which I've set up, with the DAX expression which isn't giving me the result I need.

Essentially, for each distinct 'Title'. I want the 'AnticipatedWIPFeePerMatter' column to show the sum of all the Amount values for records with that Title, multiplied by the 'LikelyBilledValue', where the LikelyBilledValue is less than 1, but the calculation should be done for each record. Apologies as this is slightly difficult to describe in words, here's an example:

In the table screenshot, we can see that there are two records that have the Title of '3'. I want the 'AnticipatedWIPFeePerMatter' to calculate the sum of (1058 * 0.67') + (432 * 0.4), so this is essentially the Amount multiplied by the LikelyBilledValue for each record with that Title, then a Sum of those values. Each record with that Title would then show the same value for that AnticipatedWIPFeePerMatter column.

Any help/advice greatly appreciated.

Many thanks, Tom

[Anonymous · Accepted solution]

Hi  @Tomburton2023 ,

According to your describe, here are my test process

To achieve your goal, you can follow these steps:

Here are my test data:

1.Create a calculate column by using DAX

 AnticipatedWIPFeePerMatter = 
CALCULATE(
    SUMX('Table','Table'[Amount]*'Table'[LikelyBilledValue]),
    FILTER('Table', 'Table'[Tiltle] = EARLIER('Table'[Tiltle]) && 'Table'[LikelyBilledValue] < 1)
)

2.Final output

^{In order for you to solve the problem faster, you can refer to the following documentation}

How to Get Your Question Answered Quickly - Microsoft Fabric Community

^{Best Regards,}

^{Albert He} 

[Tomburton2023]

Hi @Anonymous 

Thanks so, so much for your help - this is exactly what I needed! It works perfectly 🙂

Cheers, Tom