conditional sum

[klehar]

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?

[parry2k]

@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.⚡

[parry2k · Accepted solution]

@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]

@parry2k Thanks a ton.

What does @value do. Im confused

[klehar]

@parry2k 

[ryan_mayu]

@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]

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)