Forum Discussion
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 | Software | PL1 | 100 | 200 |
| Opp1 | Software | PL2 | 200 | 200 |
| Opp1 | Software | PL3 | 300 | 200 |
| Opp1 | Software | PL4 | 400 | 200 |
| Opp1 | Hardware | PL1 | 100 | 300 |
| Opp1 | Hardware | PL2 | 200 | 300 |
| Opp1 | Hardware | PL3 | 300 | 300 |
| Opp1 | Hardware | PL4 | 400 | 300 |
Opp measure = My opp value is at product line level which means I want the measure to be 100+200+300+400 = 1000
Order value measure = My order value is at Business column lebel which means I want the measure to be 200+300 = 500
How can i acheive these measures?
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.⚡
6 Replies
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?
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)
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.⚡
klehar SUMMARIZE is creating a virtual table and "@value" is just a column name to hold the max value in that virtual table. I hope it helps.
✨ 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.⚡
