Forum Discussion
Measure with Switch function with calculated percentage
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
2 Replies
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
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!!!
