Running Total - Data Source SSAS MutiDimensional Cube

Question

Hi,I would like to create a formula to calculate a Running Total in a Power BI Report. The source is a DataSet having a direct connection to an SSAS MultiDimensional Cube. I tried using Quick Measure but to no avail. Unfortunately, I would not like to solve this by creating the measure in the cube.I have search for a solution but can not seem to find anything.Can anyone help to create a DAX formula to achieve the below result with is sorted by value and not by SalesRep ?&nbsp;DATA SOURCEMULTI DIMENSIONAL CUBE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Filter 1 :&nbsp;Year(2019)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Filter 2 : SalesRep ASalesRep BSalesRep CSalesRep DSalesRep ESalesRep F&nbsp;Sort By Net Value&nbsp;&nbsp;&nbsp;&nbsp;SalesRepNameSales Net ValueRunning Total&nbsp;&nbsp;&nbsp;SalesRep E30,00030,000&nbsp;&nbsp;&nbsp;&nbsp;SalesRep B50,00080,000&nbsp;&nbsp;&nbsp;&nbsp;SalesRep A100,000180,000&nbsp;&nbsp;&nbsp;&nbsp;SalesRep D200,000380,000&nbsp;&nbsp;&nbsp;&nbsp;SalesRep C400,000780,000&nbsp;&nbsp;&nbsp;&nbsp;SalesRep F600,0001,380,000&nbsp;&nbsp;&nbsp;&nbsp;TOTAL1,380,0001,380,000&nbsp;&nbsp;&nbsp;&nbsp;

amitchandak · Answer

qwk , try like
&nbsp;
Cumm Sales = CALCULATE(SUM(Table[net Sales]),filter(all(Table),Table[net Sales] &lt;=max(Table[net Sales])))

anonymous · Answer

Hi&nbsp;qwk&nbsp;,First, please select the related visual which you want to sort the data. Then&nbsp;select its&nbsp;More options&nbsp;(...) menu in the upper right corner, select&nbsp;Sort by, and then select the field by which you want to sort.Sort by column in Power BI DesktopBest RegardsRena

qwk · Answer

Unfortunately since the data source is a multi-dimensional cube the SUM function doe not work.  Thanks

qwk · Answer

Hi, I need to create a running total fomula connecected to a muti-dimensional cube not sort by the Running total. Thanks &amp; Regards

qwk · Answer

Hi,

Is there an expert out ther who can help me solve this issue.

Please keep in mind that the data source is OLAP .. I need a Running Total Formula in Power BI on an OLAP Measure.

N.B. DAX Sum function does not work on a measure having OLAP as a data source.

Thanks

Forum Discussion

Running Total - Data Source SSAS MutiDimensional Cube

9 Replies

Related content

Running Maximum

Running sum

Running Totals

Pipeline not running

Data source

DATA SOURCE	MULTI DIMENSIONAL CUBE
Filter 1 :	Year(2019)
Filter 2 :	SalesRep A	SalesRep B	SalesRep C	SalesRep D	SalesRep E	SalesRep F
	Sort By Net Value
SalesRepName	Sales Net Value	Running Total
SalesRep E	30,000	30,000
SalesRep B	50,000	80,000
SalesRep A	100,000	180,000
SalesRep D	200,000	380,000
SalesRep C	400,000	780,000
SalesRep F	600,000	1,380,000
TOTAL	1,380,000	1,380,000