In SSAS Tabular cube, Based on the link for Clever Hierarchy Handling, we manage to build a hierarchy and address the calculation https//www.sqlbi.com/articles/clever-hierarchy-handling-in-dax/
The issue that I am facing is when I am using the other columns in the same attribute dimension the calculation is breaking. Is there any work around ? Using those columns as part of hierarchy is also not helping with the issue:
Expression :
SplitVaue := IF ( [Last 7 Month Sales] >= 1, IF ( ISFILTERED ( Dimension[RepID] ), [Last 7 Month Sales] / CALCULATE ( [Last 7 Month Sales], ALL ( Dimension[RepID] ) ) ), IF ( ISFILTERED ( ProductDim[PN] ) && ISFILTERED ( Dimension[RepID] ), CALCULATE ( [Last 7 Month Sales], ALL ( ProductDim[PN] ) ) / CALCULATE ( [Last 7 Month Sales], ALL ( Dimension[RepID] ), ALL ( ProductDim[PN] ) ) ) )
The hierarchies are as follows:
RepIDParent-> RepID
The query works fine as long as RepIDParent and RepID are selected, if I select other attributes which are part of Dimension, the above calculation doesn't work as expected.
This was not the issue in multidimensional Cubes but we are facing this problem with a tabular cube.
Thank you