Hi @aveek.das ,
Cool, you have imported and mapped data and almost there with the calculation!
The thing is, functions Members and AllMembers retrieve all dimension members, not only the lowest members as products, therefore, the sum over all dimension members is larger than the expected sum of products.
A bit of theory
In eazyBI, all dimensions are hierarchical. It means, dimensions contain not only members that are particular items (as products in your case), but also members that aggregates all items or, in more complex dimensions, also subgroups of those members; it depends how many levels the hierarchy has. Dimensions with simple hierarchies as yours have members in two levels: particular members for each product and a default member or All level member that aggregates the products. More about dimensions and members: https://docs.eazybi.com/display/EAZYBI/Main+concepts#Mainconcepts-Dimensions (and the following section).
What went wrong?
When you use [TestProduct].Members, it retrieves values from all dimension standard members (i.e., products and the default member). In your case, members Cars, Bus, and All Products are retrieved and all their values (500+200+700) are summed.
When [TestProduct].AllMembers is used, also calculate members of this dimension are retrieved and the sum is calculated over this larger set (I do not see them in your screenshots, as calculates members are in “Select individual members” sections).
What is the solution?
In your case, you may want to use the value of the Product dimension default member itself as it already contains all products. If you want to display the total value in rows against each product, you may create a measure with a tuple that overrides the price of the product with total value:
([Measures].[Price],
[TestProduct].DefaultMember)
More about tuples here: https://docs.eazybi.com/display/EAZYBI/Calculated+measures#Calculatedmeasures-Tuples
In other cases when you need to get the set of particular level members (products, in your case), you have to define the level in square brackets right after the dimension name:
[TestProduct].[TestProduct].Members
The level name you see in dimensions “All hierarchy level members” section:
Then neither the default member nor other calculated members of this dimension won’t be included in the set. Read more: https://docs.eazybi.com/display/EAZYBI/Calculated+measures+and+members#Calculatedmeasuresandmembers-Namingconventions
Feel free to ask any further questions is something in doubt!
Best,
Ilze / support@eazybi.com