Forum Discussion

GuestUser's avatar
GuestUser
Helper V
6 years ago
Solved

Running Total Issue

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

 

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    6 years ago

    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

8 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

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

    • GuestUser's avatar
      GuestUser
      Helper V
      6 years ago

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

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    6 years ago

    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/957252

     

    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's avatar
      GuestUser
      Helper V
      6 years ago

      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's avatar
        v-easonf-msft
        Community Support
        6 years ago

        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.

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    6 years ago

    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