Past this my recommendation is to really think deeply about what each part of the formula is doing in the current context. Hopefully this is the logic you are looking for. Then you have a remaining context of just those ship to regions that evaluated to TRUE and then calculated the total FYTD units. Then the VALUES part of the equation is then iterating through every single ship to location and remaining ‘in’ if the PYTD and FYTD are anything above zero. So this below is maybe all you need? CALCULATE(,įor every row in the results table, you first are filter by each product product item. Hopefully this helps walking through the logic I don’t really have a proper solution as finding it hard to imagine every moving part here without being to test it. Let’s just walk through what you have here. So just thinking aloud here, you want to work out the FYTD in units of each product if they have sales previous sales. Having this here certainly complicates the solution so I would avoid ALLSELECTED if you can. ![]() I just don’t think you need this just from looking at it. I’m pretty confident that the below part of the formula is the issueįILTER(VALUES('Fact-Sales'), 0 & 0)Īnd it’s likely not working as you would expect.Īlso I’m not sure you need this part of the formula eitherĬALCULATE(,FilterTable, ALLSELECTED(‘Dim-Product’))īecause isn’t each item part of the products table? So for each different row in the results you are just getting that one product. This is a tough one without seeing everything and testing a few things. The table was filtered for 2018 and a brand was selected. I manually computed 800 by filtering my data in excel for FYTD_ASP0 and PYTD_ASP0 for each shipTo#. (the picture is just a small extract of my entire table). The output should show 800 on every single line, but instead it shows some weird numbers and a weird total for the measure as well. There is a date slicer for FY 2018 and a brand is selected. Return CALCULATE(,FilterTable, ALLSELECTED('Dim-Product')) Var FilterTable = FILTER(VALUES('Fact-Sales'), 0 & 0) Finally, the total should be dynamic based on any slicer in the Dim-Product Table. The filtered table should filter the distinct shipTo# for FYTD_ASP and PYTD_ASP when they are greater than zero. I want to determine the total FYTD units for a filtered sales table at a transaction (shipTo#) level. If units is 0 or sales is -ve (due to a credit), ASP will be zero PYTD ASP - Gives me the corresponding period average selling price (Sales / Units). PYTD Units - Gives me the corresponding prior year to date units Let me start with the basic question and I’ll attempt to ask the question a different way just because I can see how can get lost with the formula above (some repeated info from above).įYTD Units - Gives me the 2018 fiscal year to date units of the max date selected IF( ISBLANK() || ISBLANK(), 0, / FYTD_TotSales)), Return SUMX(ADDCOLUMNS(VALUES(‘Dim-Product’), “SalesPecent”, Var FYTD_TotSales = CALCULATE(,FilteredItemTable) Compute FYTD Total Sales from filtered table required ![]() Var FilteredItemTable = FILTER(ALLSELECTED(‘Dim-Product’), 0 & 0) Create a filtered table of products without all the new, discontinued and credit/other products (the FYTD_ASP and PYTD_ASP 0, filters for these products) I just want it to always take the total of all items# sales and not segregate it by brand. While the code works on item#, the moment I add another column (such as brand) with my item# still in the first column of the table, the ALLSELECTED messes up the FYTD_TotSales by taking the cumulative brand total sales for a group of items within that brand. This measure would be shown on the table. My goal is to get % of sales (sales / Total sales) for each item# whose FYTD_ASP and PYTD_ASP 0. I have a table with rows that shows the item#. If units is 0 or sales is -ve (due to a credit), ASP will be zero. ![]() PYTD Sales - Gives me the corresponding prior year to date salesįYTD ASP - Gives me the 2018 average selling price (Sales / Units). Brand (one brand could have several item #s under it)įYTD Sales - Gives me the 2018 fiscal year to date sales of the max date selected.The moment I add more attributes to the table, the cumulative total changes but I don’t want it to do that. I want to get a cumulative total from a variable table but using the code below limits my use because of allselected.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |