Problem with calculation of total on different hierarchy levels in DAX

Anything related to PowerPivot and DAX Formuale
kirkademidov
Posts: 1
Joined: Mon Jun 11, 2018 11:27 pm

Problem with calculation of total on different hierarchy levels in DAX

Postby kirkademidov » Mon Jun 11, 2018 11:33 pm

Hi all,

I need help to find DAX solution in some hierarchy issue.



I have a model with 3 level hierarchy: Question, Model and Category Model.



I need to get some value on level of Category Model (highest level of hierarchy) which is simple sum of values on second level but it dosn't work.



Capture.PNG
Capture.PNG (11.51 KiB) Viewed 533 times







On the highest level I need simple sum of RelativeEstimated by the relative weight of model from second level of hierarchy but I`m getting 1 instead of 71.04 (17.81+26.93+26.30)

Link To PBI Solution:
https://drive.google.com/file/d/12RXwKdSLYDIh4Y8YJVD5thXsBbunEjqK/view?usp=sharing

Where is quite complicated measure to get Total Score on the second level but I can not implement it third level.



Please, anyone I really need advise - I`m trying to figure out in almost 3 weeks!

andredl
Posts: 59
Joined: Wed Sep 23, 2015 9:56 pm

Re: Problem with calculation of total on different hierarchy levels in DAX

Postby andredl » Mon Jul 02, 2018 8:57 am

Have you tried using SUMX across the values in your hierarchy for which you want the total to be aggregated? From what I can tell, Customer Attitude, Professionality and Time are all values in the column labelled SurveyQuestionsAnswers[ModelName]. You want the total (Service Quality) of RelativeEstimate to sum the values you have already calculated for the values in ModelName, but you only want to perform the sumx calculation across a distinct list of values in this column, not each and every instance of it.

RelativeEstimate = SUMX(VALUES(SurveyQuestionsAnswers[ModelName]),FactSurveyInstanceAnswers[TotalScore]*[Relative Weight])

*Please mark the solution as solved if this has helped you solve your problem*

Cheers,
André


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 6 guests

cron