Running Total Issue

[GuestUser]

Hi ,

Need help in creating running total column

We are creating report using Connect Live option - SSAS Tabular

I have report in below format

Category  Name  Sales  RTotal

ABC           XXXX   100   100

XYZ           AAAA  200     300

I need to get RTotal Column . i tried using Quick Measure - Running total but it didnt work stating MAx function can work only on numbers and not string (Category)

So need help for the same

Pls suggest

[v-easonf-msft]

Hi , @GuestUser 

Could you please tell me whether your problem has been solved?
If it is,  please mark the helpful replies or add your reply as Answered to close this thread?

Best Regards,
Community Support Team _ Eason

[v-easonf-msft]

Hi , @GuestUser 

If your want to change the type  of sales from "text"to  "decimal number",you need to do some change in SSAS model.

Please refer to this post to change your type of  sales from "text" to " decimal number" in SSAS. 

https://community.powerbi.com/t5/Desktop/Relative-Date-Filter-Analysis-Services-Live-Connection/td-p...

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GuestUser]

Hi,

I dont think isssue is with Sales Column.

Let me try to explain again :

When I select - New Quick Measure - RunningTotal . It assks for following things

Base value -  Sales Amount

Field - Category  ( It takes only columns and not measures)

Direction - Descending

and it will create measure with below formula

Measure =

CALCULATE(

    [Sales],

    FILTER(

        ALLSELECTED('Table'[Category]),

        ISONORAFTER('Table'[Category], MAX('Table'[Category]), DESC)

    )

)

Which is giving error - like MAX cannot work on String Data Types

Hope I am clear.

Any suggestions . Basically i need to achieve cumulative Total .

Any other formula /way to achieve the same ?

[v-easonf-msft]

Hi , @GuestUser 

Try   measure as  below:

Measure =
CALCULATE (
    [Sales],
    FILTER (
        ALLSELECTED ( 'Table'[Category] ),
        'Table'[Category] <= SELECTEDVALUE ( 'Table'[Category] )
    )
)

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GuestUser]

Thanks @v-easonf-msft 

Selectvalue function is not recognized in the version of dax which i am using

Any other alternative pls?

[v-easonf-msft · Accepted solution]

Hi, @GuestUser 

Please provide more details .（Where did you write your measure）

Theoretically  no matter in the directquery  or live connect mode, Selectvalue function all can be recognized in  PowerBI Desktop.

Best Regards,
Community Support Team _ Eason

[Greg_Deckler]

Sounds like you potentially need to switch Sales from being a Text field to a numeric field in your SSAS model.

[GuestUser]

Sales is a measure column which is sum of sales_amount which is numeric only