Running Total - Data Source SSAS MutiDimensional Cube

[qwk]

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 ?

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

[qwk]

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

[FarhanAhmed]

I am afraid, if you are using Live connection, you cannot create measure in Power BI Desktop

you need to create measure/calculated member in SSAS cube for running total.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d61a6355-48af-4bbd-8ceb-e65a86ac75c1/mdx-ru...

[qwk]

Thanks for your feedback.

I have created a dataset connected to a live connection to Olap using the gateway.

I build my reports of the dataset which in turn I am able to create formual on OLAP measures, simple measures or a bit more complex such as "Percentage Of Totals".

Now I need to find a way to create a running total, I am sure there is a solution.

Regards

[FarhanAhmed]

You cannot create Calculated Members in Power BI Desktop using Live connection that you normally create under Excel OLAP tools.

unfortunately, you need to create such calculations at SSAS level. 

[qwk]

If you read the full thread you will see that I am able to create fomulas with a live connection to OLAP THROUGH A DATASET USING A GATEWAY.

THANKS

[Anonymous]

Hi @qwk ,

First, please select the related visual which you want to sort the data. Then select its More options (...) menu in the upper right corner, select Sort by, and then select the field by which you want to sort.

Sort by column in Power BI Desktop

Best Regards

Rena

[qwk]

Hi,

I need to create a running total fomula connecected to a muti-dimensional cube not sort by the Running total.

Thanks & Regards

[amitchandak]

@qwk , try like

Cumm Sales = CALCULATE(SUM(Table[net Sales]),filter(all(Table),Table[net Sales] <=max(Table[net Sales])))

[qwk]

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

Thanks