Forum Discussion

stefanymonteiro's avatar
stefanymonteiro
Frequent Visitor
2 years ago
Solved

Measure with multiple conditions

Hello, just getting back to power bi after a while, and I have the following problem and data:
I have this table as if a register table

Client IdClient NameCharge?MinimumMonthly Value
1AYes10001000
2BYes20000
3CNo30000
4DYes

0

0
5EYes

0

2000


And this table as if a transactions table

Client IDClient NameValue
1A500
1A200
2B1500
2B700
3C300
4D200
5E100
5E300


For those who have Charge? = Yes, then I should see if they have a Minimum value. If they do, when the transactions SUM > Minimum, then I should Charge the SUM value. If SUM < Minimum, then I should Charge the Minimum. Also if they have a monthly value I should add it to this result (SUM+Monthly or Minimum+Monthly). If I dont have a Minimum, them I should just Charge the SUM+Monthly if they have it or just the SUM.
 

With these examples and rules, I want the following results:

I want how much I should charge for each client for those who have Charge? = Yes, so something like:

A2000
B2200
C0
D200
E2400

 

I want the sum of all charges, so something like: 6800

And I want to show how much im charging for each charge type, so something like:

Transactions2800 (only the values that passed the minimum)
Minimum1000 (only the case when transactions<minimum)
Monthly Value3000


I used this examples since I cant share my original data, but its a loooot of transactions, so using measures that would be more fluid would be better!!
Thank you in advance!!!

DAX
Need Help
Request
  • Dangar332's avatar
    Dangar332
    2 years ago

    hi, stefanymonteiro 
    stefanymonteiro 

    these output achieve 

    use below measure

    result =
    var a = max(a1[minimum])
    var b = sum(a2[value])
    var c = max(a1[charge?])="yes"
    var e = sum(a1[monthly value])
    var d = if(c=TRUE(),IF(a>b,a+e,b+e),0)
    return d
    adjust tour table name
     

    but i have question regard these 

     

     

    is there table present of these value ?

     

     

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

     

4 Replies

  • some_bih's avatar
    some_bih
    Community Champion
    2 years ago

    Hi stefanymonteiro 

    Question: register table contains only unique combinations for columns Client ID and  Client Name, like for Client A (Client ID=1) is only shown once in register table?

  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    2 years ago

    hi, stefanymonteiro 
    stefanymonteiro 

    these output achieve 

    use below measure

    result =
    var a = max(a1[minimum])
    var b = sum(a2[value])
    var c = max(a1[charge?])="yes"
    var e = sum(a1[monthly value])
    var d = if(c=TRUE(),IF(a>b,a+e,b+e),0)
    return d
    adjust tour table name
     

    but i have question regard these 

     

     

    is there table present of these value ?

     

     

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