Forum Discussion

Tomburton2023's avatar
Tomburton2023
Frequent Visitor
2 years ago
Solved

Creating a calculated column to sum values based on a condition

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

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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 

     

     

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Tomburton2023
      Frequent Visitor
      2 years ago

      Hi Anonymous 

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

      Cheers, Tom