none
Tabular model - Total to be sum instead of %

    Question

  • In Tabular model cube, i have defined Col_C =  Col_A / Col_B.

    In the grouping total, i need the Col_C  to be sum of values present in Col_C.

    It is showing currently as sum of Col_A / sum of Col_B.

    Kindly advice on how to change the grouping calculation of Col_C

    Example : Expected result is  0.19 , but i am getting 0.048 . what should i do in the tabular model design of the Col_C

    The blue color is the cumulative of the detail value.

    I need Sum(Colc) add bus, truck, car, cycle

    Sample Query Cube



    ShanmugaRaj




    Monday, July 14, 2014 7:43 AM

Answers

  • You could possibly create a calculated column in your table which calculates Col_A / Col_B for each row of the fact table. Then create a measure over the top of that column using SUM()

    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by ShanmugaRaj Tuesday, July 15, 2014 9:29 AM
    Tuesday, July 15, 2014 2:57 AM
    Moderator
  • If you create a calculated column (in the top section of the table pane) called A_div_B that is [Col_A] / [Col_B] then the expression for your measure (in the lower section of the table pane) is simply SUM( [A_div_B] )

    http://darren.gosbell.com - please mark correct answers

    Tuesday, July 15, 2014 6:29 AM
    Moderator

All replies

  • Hi ,

    Could you please let me know How this 0.19 comes??

    Like after what calculation we'll get 0.19.

    Because after Cola/ColB we will get 0.048.

    Kindly Correct me if I wrong Somewhere.

    You need Colc =ColA/ColB

    Then You need Sum(Colc)..

    Right??

    Please let me know if anything is not according to your requirement.

    Thanks



    Monday, July 14, 2014 8:08 AM
  • Is Road,Sea etc higher level grouping?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Monday, July 14, 2014 8:40 AM
  • Yes. I have updated the sample image for reference in detail.

    ShanmugaRaj

    Monday, July 14, 2014 8:51 AM
  • In Tabular model cube, i have defined Col_C =  Col_A / Col_B.

    In the grouping total, i need the Col_C  to be sum of values present in Col_C.

    It is showing currently as sum of Col_A / sum of Col_B.

    Kindly advice on how to change the grouping calculation of Col_C

    Example : Expected result is  0.19 , but i am getting 0.048 . what should i do in the tabular model design of the Col_C

    The blue color is the cumulative of the detail value.

    I need Sum(Colc) add bus, truck, car, cycle

    Sum needed to be showing in group totals

    ShanmugaRaj

    Monday, July 14, 2014 7:05 PM
  • Hi... In order to achieve the above, the data model should consists of one dimension and one fact table which contains Col_A and  Col_B as measures. Then you need to create measures

    Sum of Col_A

    Sum of Col_B

    and slice them by Item.

    If you are unable to reproduce the measures, please email me at san463@gmail.com.  I will send you the spreadsheet i created in order to achieve the below. 


    Words offer the means to meaning, and for those who will listen, the enunciation of truth - V for Vendetta.

    Monday, July 14, 2014 11:32 PM
  • You could possibly create a calculated column in your table which calculates Col_A / Col_B for each row of the fact table. Then create a measure over the top of that column using SUM()

    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by ShanmugaRaj Tuesday, July 15, 2014 9:29 AM
    Tuesday, July 15, 2014 2:57 AM
    Moderator
  • how to create a measure over the top of that column using SUM() in Tabluar model ?

    ShanmugaRaj

    Tuesday, July 15, 2014 6:25 AM
  • If you create a calculated column (in the top section of the table pane) called A_div_B that is [Col_A] / [Col_B] then the expression for your measure (in the lower section of the table pane) is simply SUM( [A_div_B] )

    http://darren.gosbell.com - please mark correct answers

    Tuesday, July 15, 2014 6:29 AM
    Moderator