Forum Discussion

klehar's avatar
klehar
Helper V
5 years ago
Solved

conditional sum

Hi, I have a stripped down data for 1 opportunity below. Actual data has 100s of opportunities. I want to create 2 measures. Opportunity Business Product Line Opp Value Order Value Opp1 ...
Need Help
  • parry2k's avatar
    parry2k
    5 years ago

    klehar add two measures:

     

    Business Value = SUMX ( SUMMARIZE ( Opp, Opp[Opportunity], Opp[Business], "@Value", MAX ( Opp[Order Value] ) ), [@Value] )

Business Value = SUMX ( SUMMARIZE ( Opp, Opp[Opportunity], Opp[Business], "@Value", MAX ( Opp[Order Value] ) ), [@Value] )

     

    ✨ Follow us on LinkedIn

     

    Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

ryan_mayu's avatar
ryan_mayu
Super User
5 years ago

klehar 

i am confused about your sample data

Opportunity Business Product Line Opp Value Order Value
Opp1 Software PL1 100

100
Opp1 Software PL2 200 200
Opp1 Software PL3 300 300
Opp1 Software PL4 400 400
Opp1 Hardware PL1 1000 500
Opp1 Hardware PL2 2000 600
Opp1 Hardware PL3 3000 700
Opp1 Hardware PL4 4000 800

what's your expected result for above sample data?

klehar's avatar
klehar
Helper V
5 years ago

hI ryan_mayu 

so i want to measures. lets call it opp value new and order value new

opp value is repeating at product line level

order value is repeating at business column level.

 

This is happening because the table is denormalised.

I dont want repititve values 

so as explaineda above 

opp value new measure = 100+200+300+400 (at product line level)

order value new measure = 200 + 300 (at software level)