Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Need Dax for below logic

Hi All,

I have Two tables Invoice and policy thse two are connected with polid

By using these two tables i need to calculate

1. Revenue

2. Emp fee

3. Broker Fee

 

Invoice table

PolIdInvNoCommission amountCommission PersonPremiumPolNo Person Type
Pol127963201700Agency1000036182363R
Pol12796320340Executive1000036182363P
Pol127963200Representative1000036182363A
Pol227963224000Agency2000052280590302R
Pol22796322800Executive2000052280590302P
Pol227963220Representative2000052280590302A
Pol12796319579.87Agency341136182363R
Pol12796319115.97Executive341136182363P
Pol127963190Representative341136182363A
Pol127963190Agency5036182363R
Pol127963190Executive5036182363P
Pol127963190Representative5036182363A
Pol127963190Agency15036182363R
Pol127963190Executive15036182363P
Pol127963190Representative15036182363R
Pol227963231000Agency500052280590302P
Pol22796323200Executive500052280590302R
Pol227963230Representative500052280590302

P

 

Policy Table

PolIdPolPIdEmployeeNameEmployee TypeEmployee Role
Pol22C08E8EB95E2479EIsaac  MatthewsExecutiveExec Primary
Pol284912CCC640541FBLisa  FlaugherRepresentativeRep Primary
Pol1DD3AED0660774D1Isaac  MatthewsExecutiveExec Primary
Pol1FAC884C9F9D94CFALisa  FlaugherRepresentative

Rep Primary

 

Result

PolNoInvNoRevenueEmp_FeeBroker FeeGrossEmployee Role
361823632796320170034001360Exec Primary
3618236327963201700001700Rep Primary
361823632796319579.87115.970463.9Exec Primary
361823632796319579.8700579.87Rep Primary
522805903022796322400080003200Exec Primary
5228059030227963224000004000Rep Primary
52280590302279632310002000800Exec Primary
5228059030227963231000001000Rep Primary

 

Revenue logic - what ever the commission is showing for commission person "Agency" that should dispaly for commission person "Employee' and "Representative"

 

Emp Fee is Pull commission paid to each employee role associated to the invoice 

 

Broker Fee is Pull commission paid to any broker associated to the invoice or commission amount of  Person Type "B"

 

Gross: Revenue - emp fee-Broker Fee 

 

Thanks in advance.

Need Help

4 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Can you please explain  each of the calculations you need?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Broker fee is Commission Amount paid to the person type "B"

       

       In the given input we don't have person type"B" so that why broker fee showing zero in the out put. but in real i have person type "B"

       

      Gross is  subtract Revenue,Emp Fee and Broker Fee

  • ERD's avatar
    ERD
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Anonymous ,

    One of the ways to achieve this result is using the next measures:

    Commission_amount = SUM ( 'T1-Invoice'[Commission amount] )
    Revenue = 
VAR polNo = SELECTEDVALUE ( 'T1-Invoice'[PolNo] )
VAR invNo = SELECTEDVALUE ( 'T1-Invoice'[InvNo] )
RETURN
    CALCULATE (
        [Commission_amount],
        'T1-Invoice'[PolNo] = polNo,
        'T1-Invoice'[InvNo] = invNo,
        'T1-Invoice'[Commission Person] = "Agency",
        ALL ( 'T1-EmpType'[Employee Role] )
    )
    Emp_fee = 
VAR polNo = SELECTEDVALUE ( 'T1-Invoice'[PolNo] )
VAR invNo = SELECTEDVALUE ( 'T1-Invoice'[InvNo] )
RETURN
    CALCULATE (
        [Commission_amount],
        'T1-Invoice'[PolNo] = polNo,
        'T1-Invoice'[InvNo] = invNo,
        'T1-Invoice'[Commission Person] <> "Agency"
    )
    Broker Fee =
VAR polNo = SELECTEDVALUE ( 'T1-Invoice'[PolNo] )
VAR invNo = SELECTEDVALUE ( 'T1-Invoice'[InvNo] )
VAR empRole = SELECTEDVALUE ( 'T1-EmpType'[Employee Type] )
RETURN
    CALCULATE (
        [Commission_amount],
        'T1-Invoice'[PolNo] = polNo,
        'T1-Invoice'[InvNo] = invNo,
        'T1-EmpType'[Employee Type] = empRole,
        'T1-Invoice'[ Person Type] = "B"
    )
    Gross = [Revenue] - [Emp_fee] - [Broker Fee]

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