I have 7 levels in my hierarchy , i want the Totals to be calculated based on what level we are currently on ,as formula changes for each levels is it possible to capture the level/grain we are at in matrix and then conditionally return the formula for that for example - if(our matrix visual is at brand grain) , sum(sales), if(our matrix visual is at plant grain) , avg(sales), if(our matrix visual is at line level grain) , sum(sales)
Hi there, thanks for your comment. If you mean the sub total (meaning higher hierarchy is total for lower one) then this is the way to go. If by total you mean grand total and in that case going to next level you mean using double down arrows in the visual to totally go to next level, you can use ISINSCOPE, however it doesn't seem to me only doing this you can show more than one type of grand total. In that case I might take the journey to do/test the calculations in a calculated table and create different totals as new categories ..
This was really helpful for me, very practical many thanks :)
Great! Thanks for watching
Fabulously applied Dax !
Thank you :)
I have 7 levels in my hierarchy , i want the Totals to be calculated based on what level we are currently on ,as formula changes for each levels is it possible to capture the level/grain we are at in matrix and then conditionally return the formula for that
for example - if(our matrix visual is at brand grain) , sum(sales),
if(our matrix visual is at plant grain) , avg(sales),
if(our matrix visual is at line level grain) , sum(sales)
Hi there, thanks for your comment. If you mean the sub total (meaning higher hierarchy is total for lower one) then this is the way to go. If by total you mean grand total and in that case going to next level you mean using double down arrows in the visual to totally go to next level, you can use ISINSCOPE, however it doesn't seem to me only doing this you can show more than one type of grand total. In that case I might take the journey to do/test the calculations in a calculated table and create different totals as new categories ..
Wow..This is what I wanted.. Thanks a bunch.. Great..
Happy it was helpful :)
Thank you so much! This was very useful for me!
Thanks fo watching. Happy it was useful :)
Thanks, this was a great lesson.
You're welcome :)
What about 3 levels in hierarcy?
Thanks for your comment. You can add conditions considering your new level in the same way.