Distribute values by column data

[Anonymous]

Hello everyone,

I have a database that when aggregated looks like this:

Product	Color	Sales	%
Car	Black	20	35.7%
Bike		6	10.7%
Bike	Black	20	35.7%
Bike	White	10	17.9%

In the bike product, i have a few bike units without color.  And i need to vizualize it without the black cell, but in a way that i don´t lose total sales. Is it possible to distribute the data proportionally inside the same product category?

The output should look like this:

Product	Color	Sales	%
Car	Black	20	35.7%
Bike	Black	24	42.9%
Bike	White	12	21.4%

Thank you,

[v-juanli-msft]

Hi @Anonymous 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[Anonymous]

Yes, thank you!

Sry for late reply, i was on vacation. 

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Assume 6 is assigned

2/3 to product bike and color blank,   ->20/(20+10)

1/3 to product bike and color white,   ->10/(20+10)

Then i create measures

v/c/pr = CALCULATE(SUM(Table1[Sales]),FILTER(ALLEXCEPT(Table1,Table1[Product]),Table1[Color]=MAX(Table1[Color])))

sum/c/pr = CALCULATE(SUM(Table1[Sales]),FILTER(ALLEXCEPT(Table1,Table1[Product]),Table1[Color]<>BLANK()))

per/col/pro = [v/c/pr]/[sum/c/pr]

sum/blank/product = CALCULATE(SUM(Table1[Sales]),FILTER(ALLEXCEPT(Table1,Table1[Product]),Table1[Color]=BLANK()))

result_sale = IF(MAX(Table1[Color])<>BLANK(),MAX(Table1[Sales])+[sum/blank/product]*[per/col/pro])

result_per = [result_sale]/CALCULATE(SUM(Table1[Sales]),ALL(Table1))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.