Applying Count/Distinct Count operation on Dimension

Malav Shelat
Malav Shelat Customer
Second Anniversary
in Platform

Hello there,

 

I am having little trouble finding a data operation which can reflect my dimensional values in form of count or lets say numbers,

 

For example, I have two Columns like ,

 

DimensionMeasure
Bldg110
Bldg220
Bldg350
Bldg470

  

 

What i want to get is Total Sum of Measures / Total Count of Dimensions and average it out, 150/4 = 37.5

how can i do this on Board, help requested

 

Note: Dimension is in Entity, Measure is in Cube

 

Thanks & Regards,

Malav

Answers

  • Alexander Kappes
    Alexander Kappes Employee
    Fourth Anniversary 250 Likes 100 Comments 5 Answers
    edited March 2020

    Dear Malav Shelat,

     

    the best solution for you depends to your case.

     

    When you just want to display, you can use the DownTotal from our DataView Object. In general we also have some further options, which can be used for the totals.

     

    image

     

    For the case you want to calculate with this Total, we have the commands (dt, gt, rt) which can be used in Column Algorithm. Some more details you can find here. (Column Algorithm )

     

    Hope it helps.

     

    regards

     

    Alexander Kappes

  • Previous Member
    Previous Member
    500 Up Votes 500 Likes 500 Comments Second Anniversary

    Hello Alexander Kappes ,

     

    Dimension Column is an entity , i am aware of the above solution you gave, what i am looking for is Count/distinct Count of an entity column, For example,

     

    lets say i have Customers Column as an entity(Dimensional Value)

    and SalesAmount in Cube (Measure )

     

    now what shall i do if i want to find what numbers of Customers has produced 'X' sales amount , for that i need to count the number of Customers, which i am not able to do rightnow.

     

    Did i put that right ?

     

    looking for further help,

     

    Thanks

  • Alexander Kappes
    Alexander Kappes Employee
    Fourth Anniversary 250 Likes 100 Comments 5 Answers

    Dear Malav Shelat,

     

    you can do the following. You insert a column algorithm just with the value 1 inside your layout. Now for each row, you see the value 1 and can also build a total about this.

     

    In general you can also think about using MXC Cubes, which are special counter cubes can be used directly by dataloading from source system. MXC data type 

     

    Hope this helps.

     

    regards

     

    Alexander Kappes

  • Previous Member
    Previous Member
    500 Up Votes 500 Likes 500 Comments Second Anniversary

    Yes Alexander Kappes,

     

    MXC will be the correct process to follow on this because if i insert value 1 using algorithm in the layout then i will not be able to get distinct count,

     

    Thank you so much for your help Alexander,

     

    Thanks and Regards,

    Malav

Categories