Fetch last non-blank value for any date with multiple filters
I am trying to produce the sum of a running total for all SKU's selected by slicers on any selected date. I have a table with transaction data as follows: Warehouse | SKU | TxDate | DayQty | RT (Running Total) This measure works for each SKU individually: RT by Size = VAR d = SELECTEDVALUE('Date'[Date]) RETURN CALCULATE( LASTNONBLANKVALUE(StkDayQty[TxDate], SUM(StkDayQty[RT])), StkDayQty[TxDate] <= d, CROSSFILTER('Date'[Date], StkDayQty[TxDate], none) ) I created a table that assigns a unique ID (IdWhseStk) for each SKU in each warehouse and attempted to create a measure that will sum RT for all selected by slicers: RT Total = SUMX( VALUES(WhseStk[IdWhseStk]), [RT by Size]) This does not work: Also tried this with the same result: RT Selected = VAR _RTSize = [RT by Size] RETURN if( isfiltered(WhseStk[IdWhseStk]) , SUMX ( VALUES ( WhseStk[IdWhseStk] ),_RTSize)) I even tried to merge the entire date table to each SKU before calculating the RT in Power Query so that I have a distinct RT for every SKU on every date but it results in millions of rows. I can't find a solution that I can apply to my dataset. Any advice would be much appreciated.
Measure totalling issue
Hello Community 🙂 I am building a table to calculate the sales bonus earned by salesperson and quarter and having trouble with totalling a measure. The possible sales bonus value is based on target bands for their annual sales target and being awarded the bonus is based on hitting both sales and profit targets for the quarter. You can see from the screenshot that I have had similar issues bdmActualSalesQtr and bdmActualGpQtr measures I was able to solve them thanks to Greg_Deckler Measure Totals, The Final Word - Microsoft Fabric Community and others like How to Make Measures Total Correctly in Power BI Tables - ArcherPoint bdmActualSalesQtr = CALCULATE( SUM(Sales[Total Sales]), Sales[User Code]=SELECTEDVALUE('Incentives BDM Quarterly'[User Code]), 'Date'[Fiscal Quarter Year]=SELECTEDVALUE('Incentives BDM Quarterly'[Fiscal Quarter Year]) ) BDM Actual Sales Qtr = VAR __table = SUMMARIZE(Sales, 'Date'[Fiscal Quarter Year], "__value", SUM(Sales[Total Sales])) RETURN IF( COUNTROWS('Incentives BDM Quarterly') = BLANK(), BLANK(), IF( COUNTROWS('Incentives BDM Quarterly') = 1, 'Incentives BDM Quarterly'[bdmActualSalesQtr], SUMX(__table, [__value]) ) ) But I can't get the actual bonus earned to work bdmActualBonusQtr = IF( COUNTROWS('Incentives BDM Quarterly') = BLANK(), BLANK(), IF( 'Incentives BDM Quarterly'[BDM % GP Target] >=1 && 'Incentives BDM Quarterly'[BDM % Sales Target] >=1, SUM('Incentives BDM Quarterly'[BDM Sales Band Bonus Qtr]), 0 ) ) BDM Actual Bonus Qtr = VAR __table = SUMMARIZE('Incentives BDM Quarterly', [Fiscal Quarter Year], "__value", [bdmActualBonusQtr]) RETURN IF( COUNTROWS('Incentives BDM Quarterly') = BLANK(), BLANK(), IF( COUNTROWS('Incentives BDM Quarterly') = 1, 'Incentives BDM Quarterly'[bdmActualBonusQtr], SUMX(__table, [__value]) ) ) I really can't afford to lose any more hair over this!! Thank you for your consideration. d;)
Grand Total Not Correct after Using REMOVEFILTERS
Hi, I am new to Power BI but I am constantly learning. I have tried so many different ways (posts, Youtube, etc) of solving this issue and am having no luck. Any help would be greatly appreciated. I am trying to create a drill down to item level data to include items purchased by a vendor selected in the summary, the purchase amounts of those items and the sales amounts of those items. I used removefilters in the sales formula to remove the "source no." from the drill through filter. The source no is the vendor no or the customer no depending on the document type. This works fine for the line totals but the grand total is the sum of all items for the time period. Please see my pbix file here: TestFileMeasure Totals in a table visual using Field Parameters
Hello, community! Is there a way to dynamically calculate totals in a table using Field Parameters? I have the following data: The following Field Parameter: Parameter - Dimensions = { ("Category1", NAMEOF('Table'[Category1]), 0), ("Category2", NAMEOF('Table'[Category2]), 1) } And the following measures: .Sum = SUM('Table'[Value])-50 .Sum Field Parameter = VAR __table = SUMMARIZE( 'Parameter - Dimensions', 'Parameter - Dimensions'[Parameter - Dimensions Fields], "@row",[.Sum] ) RETURN IF( HASONEVALUE('Parameter - Dimensions'[Parameter - Dimensions Fields]), [.Sum], SUMX(__table,[@row]) ) And this is the result (3500) I was expecting for my second measure on a Matrix visual: And the result I would expect if selecting Category1 (3450): PS. if I use the following .Sum Field Parameter := SUMX( VALUES('Table'[Category1]), [.Sum] ) I have the desired result for the total row (which is the sum of rows). But I would like to dynamically change the column in the SUMX to whatever is selected on the field parameter if possible. Greg_Deckler
