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:...
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!!!