DAX Running Total (ASC) by category
Hi, I have my data broken up into 100 groups and I want to know how much of an amount is left from the current row to the last row (100). The formula works well when I only have one state in the visual. If I add multiple states it breaks down. I have tried a couple of methods at this point, one uses IsOnOrAfter and the other just a filter between current row and row 100. I figure I am missing some kind of command with the [StoreState] Field but I am unsure how to proceed. Can someone help me calculate this running total by category? To Go Amount= CALCULATE( SUM('Table'[Amount]), FILTER( ALLSELECTED('Table'[StatePercentile]), ISONORAFTER('Table'[StatePercentile], MIN('Table'[StatePercentile]), asc) ) )
Need measures that will work with a larger dataset (out of memory errors)
I have 4 working measures prefixed with # in this example Power BI report. The measures work fine with the example data but when I use real data (approx. 90 million imported rows with filters applied to reduce the dataset for scenario testing) I get out of memory errors on some visuals, unless I apply more filters to reduce the dataset. Is there a way to re-write these measures so that they will work better with a large dataset? Measure 1 is an interim measure to calculate Measure 2: #RT = CALCULATE( SUMX( ADDCOLUMNS( SUMMARIZE( DummyDataID, DummyDataID[ID] ), "ExAmt", [Exceeded Amount] ), [ExAmt] ), FILTER(ALLSELECTED(DummyDataID), DummyDataID[ID] <= MAX(DummyDataDemand[ID])) ) Measure 2 #RT_filtered = VAR currentID = SELECTEDVALUE ( DummyDataID[ID] ) VAR firstID = MINX ( ALLSELECTED ( DummyDataID ), DummyDataID[ID] ) VAR minValue = MINX ( FILTER ( DummyDataID, DummyDataID[ID] = firstID ), [Exceeded Amount] ) VAR minOfSum = MIN ( 0, MINX ( FILTER ( ALLSELECTED ( DummyDataID ), DummyDataID[ID] <= currentID ), [#RT] ) ) RETURN IF ( currentID = firstID && minValue < 0, [#RT] - minValue, [#RT] - minOfSum ) Measure 3 is the sum of the values from Measure 2 #RT with sum = IF(HASONEVALUE(DummyDataID[ID]), [#RT_filtered], SUMX(VALUES(DummyDataID[ID]), [#RT_filtered])) Measure 4 is the highest value #RT max = MAXX( ADDCOLUMNS( SUMMARIZE(DummyDataID, DummyDataID[ID]), "@RT", [#RT with sum] ), [#RT with sum] )
