Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi i have data with many rows and orders
an order number can be used to have multiple products so there will be multiple rows of a duplicated sales order number
i want to be able to filter orders that contain a certain product from a certain manufacturer to further analyse those orders and their full order value.
i added a custom column so that i can identify orders that have a manufacturer of x and a level 3 hierarchy of y see below:
if[Manufacturer] = "Yazoo" and [Hierarchy Level 3]= "CONF" then "1" else "0"
However i need to get the value of everything on the order and not just the items flagged 1 or 0.
Can anyone help?
Example data:
Sum of Final Amount (£) | Revenue Group | Hierarchy Level 1 | Hierarchy Level 2 | Hierarchy Level 3 | Period | Manufacturer | Entity Code | Entry Number | Entry Type | Year Ending | Part Number | Description | Invoice Number | Amount | Final Amount | Currency Code | Exchange Rate | Amount (£) | Adjusted Amount (£) | Customer Number | Product Type | Sales Order Number | Pre Invoice | Created Date | Is Internal Transfer | Configuration |
£5 | Services | Metal | CONFIGURATION | CONFIGURATION | 1 | YAZOO | UK | 15298602 | Item | 2017 | AHHH | HIP FLASK | PSINV0179 | 0 | 0 | GBP | 1 | 0 | 0 | 23487 | SERVICES | SOR1234 | 0 | 01/02/17 09:43 | No | 1 |
£10 | Services | Pet | CONFIGURATION | CONFIGURATION | 1 | TEN | UK | 15298603 | Item | 2017 | AKKK | DOG TAG | PSINV01 | 0 | 0 | GBP | 1 | 0 | 0 | 23487 | SERVICES | SOR1234 | 0 | 01/02/17 09:43 | No | 0 |
Solved! Go to Solution.
Hi @ballist1x,
I have been looking at your question and looking at the setup and with making some "ugly" coding you can do the following:
Orders_Config_Summary = FILTER ( SUMMARIZE ( ALL ( Table1[MANUFACTURER]; Table1[Hierarchy Level 3]; Table1[SALES_ORDER_NO] ); Table1[SALES_ORDER_NO]; Table1[MANUFACTURER]; [Hierarchy Level 3] ); Table1[MANUFACTURER] = "YAZOO" && Table1[Hierarchy Level 3] = "CONFIG" )
Config_Data = IF ( LOOKUPVALUE ( Orders_Config_Summary[SALES_ORDER_NO]; Orders_Config_Summary[SALES_ORDER_NO]; Table1[SALES_ORDER_NO] ) = BLANK (); "NOCONFIG"; Table1[SALES_ORDER_NO] )
Again this is very "ugly" coding and it's hard coded in the summary table the manufacturer and Hierarchy level, don't know if you need to have any changes of this to another manufacturer or hierarchy configuration but it's working with the information you gave.
See attach the pbix file (we tranfers only available for 7 days) in page 2 is this solution,
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @ballist1x,
Add the following calculated column:
SORD OK = IF ( ISBLANK ( LOOKUPVALUE ( Orders_Config_Summary[SALES_ORDER_NO]; Orders_Config_Summary[SALES_ORDER_NO]; Table1[SALES_ORDER_NO] ) ); "NO"; "YES" )
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @ballist1x,
Can you please clarify better.
It's seem to mee that you don't need to make a calculated column but create a table with the manufacturer and the hierarchy levels, however for me your question is not tottally clear.
Can you have some additional details / expected results.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
Sure that is a possibility.
i want to be able to get all of the data for orders with config lines added to them, and exclude all orders with no config in them, to work out which do and which do not.
i want to be able to calculate the final amount £ and see all of the details of row of an order but only for orders with config.
Regards
Hi @ballist1x,
sorry for asking but can you give some sample data and expected result.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix the data sample has been pasted into the original post.
the expected result is that i can return all the rowds that have a shared SORD number with an order that has [Manufacturer] = "Yazoo" and [Hierarchy Level 3]= "CONF" then "1" else "0"
there are many SORDS and many lines within each order.
not every order has a line with CONFIG as the Hierarchy level 3.
i need to get all the lines group together that share an SORD with an Entry number that contains Hierarchy Level 3 CONFIG
Hi @ballist1x,
The sample is very reduced so to get the result you want it's difficult taking into account all the constrainments you identify on your post.
Just want to help you in the best way I can and with just two lines of information as you can imagine it's dificcult to get the final result.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi No problem, see below:
ENTITY_CODE | SOURCE_ENTRY_NO | SOURCE_ENTRY_TYPE | YEAR_ENDING | PERIOD | POSTING_DATE | NAV_NO_CODE | NAV_NO_DESCRIPTION | DOCUMENT_NO | QUANTITY | Sell | Cost | margin | SOURCE_NO | CUSTOMER_NAME | TYPE_OF_SUPPLY_CODE | MANUFACTURER | SALES_ORDER_NO | PRE_INVOICE | CREATED_DATE | MODIFIED_DATE | Revenue Group | Hierarchy Level 1 | Hierarchy Level 2 | Hierarchy Level 3 |
SG | 54733 | Item | 2018 | 1 | 01/01/2018 | NXG | PET1 | SG1111 | 1 | 20 | 10 | 10 | SH1123 | DOG BZ | IT | TINNED | SORD1 | 0 | 01/02/2018 03:03 | FOOD | FOOD | FOOD | FOOD | |
SG | 54734 | Item | 2018 | 1 | 01/01/2018 | NXG | PET3 | SG1113 | 6 | 20 | 10 | 10 | SH1125 | CATZ | IT | TUNA | SORD2 | 0 | 01/02/2018 03:03 | FOOD | FOOD | FOOD | FOOD | |
SG | 54735 | Item | 2018 | 1 | 01/01/2018 | NXG | PET5 | SG1115 | 24 | 10 | 5 | 5 | SH1127 | CATZ | IT | YAZOO | SORD3 | 0 | 01/02/2018 03:03 | FOOD | FOOD | CONFIG | CONFIG | |
SG | 54736 | Item | 2018 | 1 | 01/01/2018 | NXG | PET8 | SG1118 | 6 | 20 | 10 | 10 | SH1130 | DOG BZ | IT | TINNED | SORD3 | 0 | 01/02/2018 03:03 | FOOD | FOOD | FOOD | FOOD | |
SG | 54737 | Item | 2018 | 1 | 01/01/2018 | NXG | PET9 | SG1119 | 3 | 10 | 5 | 5 | SH1131 | BINNY | IT | YAZOO | SORD4 | 0 | 01/02/2018 03:03 | FOOD | FOOD | CONFIG | CONFIG | |
SG | 54738 | Item | 2018 | 1 | 01/01/2018 | NXG | PET12 | SG1122 | 3 | 20 | 10 | 10 | SH1134 | BINNY | IT | TINNED | SORD4 | 0 | 01/02/2018 03:03 | FOOD | FOOD | FOOD | FOOD | |
SG | 54739 | Item | 2018 | 1 | 01/01/2018 | NXG | PET14 | SG1124 | 1 | 20 | 10 | 10 | SH1136 | DOG BZ | IT | TUNA | SORD5 | 0 | 01/02/2018 03:03 | FOOD | FOOD | FOOD | FOOD | |
SG | 54740 | Item | 2018 | 1 | 01/01/2018 | NXG | PET15 | SG1125 | 3 | 20 | 10 | 10 | SH1137 | BINNY | IT | TINNED | SORD6 | 0 | 01/02/2018 03:03 | FOOD | FOOD | FOOD | FOOD | |
SG | 54741 | Item | 2018 | 1 | 01/01/2018 | NXG | PET18 | SG1128 | 1 | 20 | 10 | 10 | SH1140 | DOG BZ | IT | YAZOO | SORD7 | 0 | 01/02/2018 03:03 | FOOD | FOOD | FOOD | FOOD |
As you can see we have multiple SALES_ORDER_NO's. Some of those sales orders have more than 1 row on the same SALES_ORDER_NO.
I need to find a way to be able to use a slicer or a filter so that i can find just examples where Manufacturer is YAZOO AND Hierarchy Level 3 = CONFIG
and then return all of the rows with the same SALES_ORDER_NUMBER, i.e in this example SALES_ORDER_NUMBER; SORD3 showing both SOURCE SOURCE_ENTRY_NO and entire rows for these lines so that i can calculate all of the values for these.
HI @ballist1x,
I was abble to do the presentation of the values based on slicers and this is working, based on the interactions between the tables.
However I have some addtional questions are you calculating KPI, charts etc, based on the selections? I'm asking this because the way the measure are calculated need to be inline with your selections.
To do this follow this steps:
Based on this make a selection of manufacturer and hierarchy level, then select the orders in the sales order number and your table will be updated with all the lines you need:
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português
thanks mfelix, thats the outcome that i want to get to but i want to be able to use a single filter or slicer to be able to pull the data to show for example the total revenue, margin, cost for those orders with Config listed in them by using a single selection.
this is why i wamted to write either DAX or Powerquery or create a new table that can basically find and display all of the orders that match that criteria.
for example, if i have the following visualisations, i need them to be filtered to show data where we have created the slicers as above but in one single query
is there a way i can upload an attachment?
Hi @ballist1x,
I have been looking at your question and looking at the setup and with making some "ugly" coding you can do the following:
Orders_Config_Summary = FILTER ( SUMMARIZE ( ALL ( Table1[MANUFACTURER]; Table1[Hierarchy Level 3]; Table1[SALES_ORDER_NO] ); Table1[SALES_ORDER_NO]; Table1[MANUFACTURER]; [Hierarchy Level 3] ); Table1[MANUFACTURER] = "YAZOO" && Table1[Hierarchy Level 3] = "CONFIG" )
Config_Data = IF ( LOOKUPVALUE ( Orders_Config_Summary[SALES_ORDER_NO]; Orders_Config_Summary[SALES_ORDER_NO]; Table1[SALES_ORDER_NO] ) = BLANK (); "NOCONFIG"; Table1[SALES_ORDER_NO] )
Again this is very "ugly" coding and it's hard coded in the summary table the manufacturer and Hierarchy level, don't know if you need to have any changes of this to another manufacturer or hierarchy configuration but it's working with the information you gave.
See attach the pbix file (we tranfers only available for 7 days) in page 2 is this solution,
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português
Hi, i now have this working as you described and created the extra table.
Now what i need to do is to go back to table1, and say if SORD is in the new table Orders_Config_Summary then put a YES into a new column in table1 next to every entry number row where that SORD matches the SORD in the Orders_Config_Summary...
Hi @ballist1x,
Add the following calculated column:
SORD OK = IF ( ISBLANK ( LOOKUPVALUE ( Orders_Config_Summary[SALES_ORDER_NO]; Orders_Config_Summary[SALES_ORDER_NO]; Table1[SALES_ORDER_NO] ) ); "NO"; "YES" )
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @ballist1x,
You can add a file trhough a link of onedrive, google drive or wetransfer.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Portuguêsit okay it allowed me to attach a pic.
thats the kind of dashboard i have and i need to find a way to use a filter to simply show me ALL of the orders, or ALL of the orders that contain one entry number which includes Manufacturer Yazoo and Hierarchy 3 CONFIG
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |