Measure with Switch function with calculated percentage

[LuisaCruz20]

I have a report for a Flower Company with 5 tables for data which are Finalcial, Budget, Sales 2017, Sales 2018 and Dispatches, these are conected to some translators for the codes of the sistem for: Customer, Flowers type and colors, Dates per period and Weeks and Type of Product. I have the following problem:

On the budget and Financial there is a color we call "assorted" and we usw it for some bouquets we sell that does not require an specific color. However, on the dispatch data we do not have assorted colors, there we have the real colors. My problem is, when I compare the budget with the sales I have a big difference between the colors and the assorted. I though with switch function in  measure it will be possible but, I could not use the "assorted" as value because it had to be a number. I need to conver the assorted like this below:

ALSTROEMERIA
Assorted 100% -158 K

COLOR %
Cherry 13% -20,532
Green 1% -1,579
Lavender 4% -6,318
Magenta 3% -4,738
Orange 13% -20,532
Pink 14% -22,112
Purple 10% -15,794
Red 16% -25,271
White 15% -23,691
Yellow 11% -17,374
-
100% -157,941


POMS
Assorted 100% -158 K

COLOR %
White 37% -58,438
Yellow 28% -44,223
Magenta 13% -20,532
Green 12% -18,953
Lavender 6% -9,476
Purple 3% -4,738
Cream 1% -1,579
100% -157,941

[v-yulgu-msft]

Hi @LuisaCruz20,

From current description, I am not clear about what you were attempting to achieve. What is the source table? Could you provide some dummy data to show table structure and the relationship between tables? What is your desired result? Please illustrate with screenshot. How to Get Your Question Answered Quickly

Regards,

Yuliana Gu

[LuisaCruz20]

Yuliana, 

Please see below the tables:

Data:

1. Budget:

DATE	Component No.	STEMS	Location Code	Customer Label Box Code	Production Location	Farm Production Date	Shipment Date
02/28/2018	FALST063	3.164,00	NJ	A13	JF	04/03/2018	04/10/2018
02/27/2018	FALST064	3.164,00	NJ	A13	JF	04/03/2018	04/10/2018
02/27/2018	FALST065	2.712,00	NJ	A13	JF	04/03/2018	04/10/2018
02/27/2018	FALST066	2.712,00	NJ	A13	JF	04/03/2018	04/10/2018
02/27/2018	FALST067	904	NJ	A13	JF	04/03/2018	04/10/2018
02/27/2018	FALST068	552	NJ	A13	JF	04/03/2018	04/10/2018
02/27/2018	FALST001	276	NJ	A13	JF	04/03/2018	04/10/2018
02/27/2018	FALST036	552	NJ	A13	JF	04/03/2018	04/10/2018
02/27/2018	FALST002	414	NJ	A13	JF	04/03/2018	04/10/2018
02/27/2018	FALST097	276	NJ	A13	JF	04/03/2018	04/10/2018
02/27/2018	FALST098	552	NJ	A13	JF	04/03/2018	04/10/2018
02/27/2018	FALST031	414	NJ	A13	JF	04/03/2018	04/10/2018
02/27/2018	FALST316	414	NJ	A13	JF	04/03/2018	04/10/2018
02/27/2018	FALST003	414	NJ	A13	JF	04/03/2018	04/10/2018
02/27/2018	FALST235	414	NJ	A13	JF	04/03/2018	04/10/2018
03/02/2018	FALST292	276	NJ	A13	JF	04/03/2018	04/10/2018
04/09/2018	FALST004	414	NJ	A13	JF	04/03/2018	04/10/2018
04/09/2018	FALST099	60	NJ	A13	JF	04/03/2018	04/10/2018
04/09/2018	FALST100	40	NJ	A13	JF	04/03/2018	04/10/2018

 2.  Actual Sales 

DATE	Component No.	STEMS	Location Code	Customer Label Box Code	Production Location	Farm Production Date	Shipment Date
02/28/2018	FALST063	1608	NJ	FF01	JF	02/25/2018	03/03/2018
02/27/2018	FALST064	1608	NJ	FF01	JF	02/24/2018	03/02/2018
02/27/2018	FALST065	1608	NJ	FF01	JF	02/24/2018	03/02/2018
02/27/2018	FALST066	1608	NJ	FF01	JF	02/24/2018	03/02/2018
02/27/2018	FALST067	1608	NJ	FF01	JF	02/24/2018	03/02/2018
02/27/2018	FALST068	1950	NJ	FF01	JF	02/24/2018	03/02/2018
02/27/2018	FALST001	585	NJ	FF01	JF	02/24/2018	03/02/2018
02/27/2018	FALST036	585	NJ	FF01	JF	02/24/2018	03/02/2018
02/27/2018	FALST002	1170	NJ	FF01	JF	02/24/2018	03/02/2018
02/27/2018	FALST097	1170	NJ	FF01	JF	02/24/2018	03/02/2018
02/27/2018	FALST098	360	NJ	FF01	JF	02/24/2018	03/02/2018
02/27/2018	FALST031	216	NJ	FF01	JF	02/24/2018	03/02/2018
02/27/2018	FALST316	288	NJ	FF01	JF	02/24/2018	03/02/2018
02/27/2018	FALST003	216	NJ	FF01	JF	02/24/2018	03/02/2018
02/27/2018	FALST235	144	NJ	FF01	JF	02/24/2018	03/02/2018
03/02/2018	FALST292	288	NJ	FF01	JF	02/27/2018	03/05/2018
04/09/2018	FALST004	216	NJ	FF01	JF	04/06/2018	04/12/2018
04/09/2018	FALST099	216	NJ	FF01	JF	04/06/2018	04/12/2018
04/09/2018	FALST100	1734	NJ	FF01	JF	04/06/2018	04/12/2018

5. Real Dispatch:

DATE	Component No.	STEMS	Location Code	Customer Label Box Code	Production Location	Farm Production Date	Shipment Date
02/28/2018	FALST063	1704	NJ	FF01	JF	02/25/2018	03/03/2018
02/27/2018	FALST064	1278	NJ	FF01	JF	02/24/2018	03/02/2018
02/27/2018	FALST065	1278	NJ	FF01	JF	02/24/2018	03/02/2018
02/27/2018	FALST066	1136	NJ	FF01	JF	02/24/2018	03/02/2018
02/27/2018	FALST067	852	NJ	FF01	JF	02/24/2018	03/02/2018
02/27/2018	FALST068	852	NJ	FF01	JF	02/24/2018	03/02/2018
02/27/2018	FALST036	852	NJ	FF01	JF	02/24/2018	03/02/2018
02/27/2018	FALST002	852	NJ	FF01	JF	02/24/2018	03/02/2018
02/27/2018	FALST097	852	NJ	FF01	JF	02/24/2018	03/02/2018
02/27/2018	FALST064	852	NJ	FF01	JF	02/24/2018	03/02/2018
02/27/2018	FALST065	852	NJ	FF01	JF	02/24/2018	03/02/2018
02/27/2018	FALST064	568	NJ	FF01	JF	02/24/2018	03/02/2018
02/27/2018	FALST065	568	NJ	FF01	JF	02/24/2018	03/02/2018
02/27/2018	FALST068	568	NJ	FF01	JF	02/24/2018	03/02/2018
02/27/2018	FALST068	568	NJ	FF01	JF	02/24/2018	03/02/2018
03/02/2018	FALST036	426	NJ	FF01	JF	02/27/2018	03/05/2018
04/09/2018	FALST004	426	NJ	FF01	JF	04/06/2018	04/12/2018
04/09/2018	FALST099	426	NJ	FF01	JF	04/06/2018	04/12/2018
04/09/2018	FALST100	426	NJ	FF01	JF	04/06/2018	04/12/2018

Connector:

No_	Description	Type	Color	Category	Base Color
FALST063	ALSTRO - 5 ST - HOT PINK	ALSTRO	HOT PINK	PRIMARY	HOT PINK
FALST064	ALSTRO - 5 ST - ORANGE	ALSTRO	ORANGE	PRIMARY	ORANGE
FALST065	ALSTRO - 5 ST - PINK	ALSTRO	PINK	PRIMARY	PINK
FALST066	ALSTRO - 5 ST - PURPLE	ALSTRO	PURPLE	PRIMARY	PURPLE
FALST067	ALSTRO - 5 ST - RED	ALSTRO	RED	PRIMARY	RED
FALST068	ALSTRO - 5 ST - YELLOW	ALSTRO	YELLOW	PRIMARY	YELLOW
FALST001	ALSTRO FANCY ASSORT	ALSTRO	ASSORT	PRIMARY	ASSORT
FALST036	ALSTRO FANCY BURGUNDY	ALSTRO	BURGUNDY	PRIMARY	BURGUNDY
FALST002	ALSTRO FANCY CHARM	ALSTRO	CHARM	PRIMARY	CHARM
FALST097	ALSTRO FANCY CHERRY	ALSTRO	CHERRY	PRIMARY	HOT PINK
FALST098	ALSTRO FANCY CORAL	ALSTRO	CORAL	PRIMARY	CORAL
FALST031	ALSTRO FANCY FALL PACK	ALSTRO	FALL PACK	PRIMARY	ASSORT
FALST316	ALSTRO FANCY GREEN SHAKIRA	ALSTRO	GREEN SHAKIRA	PRIMARY	GREEN
FALST003	ALSTRO FANCY HOT PINK	ALSTRO	HOT PINK	PRIMARY	HOT PINK
FALST235	ALSTRO FANCY HOT PINK PTD ORAN	ALSTRO	HOT PINK PTD ORANGE	PRIMARY	HOT PINK
FALST292	ALSTRO FANCY HOT PINK PTD PINK	ALSTRO	HOT PINK PTD PINK	PRIMARY	HOT PINK
FALST004	ALSTRO FANCY LAVENDER	ALSTRO	LAVENDER	PRIMARY	LAVENDER
FALST099	ALSTRO FANCY MAGENTA	ALSTRO	MAGENTA	PRIMARY	MAGENTA
FALST100	ALSTRO FANCY MAGENTA LIGHT	ALSTRO	MAGENTA LIGHT	PRIMARY	MAGENTA

When you port the tables to Power Bi and you conecthem and create a grafic to compare the stems by color,  you will se the Dispatch table does not have Assorted Color. What I need to do is to create a measure where I can divide the stems of assorted colors of the firts 4 tables to the other colors of Alstro in the percentages below:

ALSTROEMERIA
	Assorted	100%	-                184K
	COLOR	%
ALSTRO	HOT PINK	13%	-                24 K
ALSTRO	GREEN	1%	-                  2 K
ALSTRO	LAVENDER	4%	-                  7 K
ALSTRO	MAGENTA	3%	-                  6 K
ALSTRO	ORANGE	13%	-                24 K
ALSTRO	PINK	14%	-                26 K
ALSTRO	PURPLE	10%	-                18 K
ALSTRO	RED	16%	-                29 K
ALSTRO	WHITE	15%	-                28 K
ALSTRO	YELLOW	11%	-                20 K
			-
	Total	100%	-       184K

these in order to compare the budget and other files with the real dispatch in the same portion. I tried with Switch function but it only accepts numerical values as the reference; if you can help me, I will appreciate it!!!