[HELP] How to Create a measure with Sum If

[tiago]

Product	Color	Sales	Stock	Total Ordered (Sales + Stock)	% Sales / Total Order
A	Black	100	200	300	33%
A	Gray	200	100	300	67%
A	Blue	300	50	350	86%
B	Yellow	100	30	130	77%

I need to create the Total Ordered (Sales + Stock Measure) and the % Sales/Total Order measure

What I am trying to do is 

Measure = DIVIDE(SUM('TABLE'[SALES]);(SUM('TABLE'[SALES])+SUM(TABLE[STOCK])))

It is not working because this view is shown by color, and the way I've done it it SUMS all product A sales and divides per all product A sales + stock. I needed to get the sales per color.

Any help is appreciated.

Thanks in advance,

Regards,

Tiago

[v-yulgu-msft]

Hi @tiago,

I tested with your provided measure, it returned the same result as shown in above sample table.

Is this your expected result? Would you please share your sample .pbix file for a test?

Regards,

Yuliana Gu

[themistoklis]

@tiago

use ALLEXCEPT fuction to divide

Measure = DIVIDE(SUM('TABLE'[SALES]); CALCULATE((SUM('TABLE'[SALES])+SUM(TABLE[STOCK])), ALLEXCEPT('TABLENAME', TABLENAME[COLOR])

[tiago]

If I isolate only one product.  and use this measure.

Measure =  DIVIDE(SUM('TABLE'[SALES]);(SUM('TABLE'[SALES])+SUM(TABLE[STOCK])))

It returns many lines with duplicated values but with the correct calculation. But something is wrong with the measure, becuse when I add it to my table, the table that has 1 product and 3 colors explodes to have many lines. 

There is probably a proper way to do this.

Regards,

Tiago

[tiago]

Hi guys, couldnt figure this one out still. Please help