Forum Discussion
Hierarchical Formula Calculation
Thank you, however I need the formula to be more dynamic.
I will have a list of thousands of individuals each with a unique set of codes. I would like the formula to auto calculate the sum rather than having to select the codes individually in the selection box you provided.
After looking at your file more closely i belive what you did is correct, would you be able to provide addtional steps on how you created these formulas. I am having trouble following.
Thank you!
First,Split the columns in query editor.Then create the two columns in the table.
Max = MAX(MAX(MAX(Code[Secondary Hierarch .1],Code[Secondary Hierarch .2]),Code[Secondary Hierarch .3]),Code[Secondary Hierarch .4])
Hierarchy = CALCULATE(MAX(Code[Max]),FILTER(Code,Code[Code list]<=EARLIER(Code[Code list])))
Last,create the below measures to get the codes.
min_list = MINX(FILTER(ALLSELECTED(Code),Code[Hierarchy]=MAX(Code[Hierarchy])),Code[Code list])
Codes = IF(ISBLANK(SUM(Code[Secondary Hierarch .1])),SUM(Code[Value of Code]),CALCULATE(SUM(Code[Value of Code]),FILTER(Code,Code[Code list]=[min_list])))
Regards,
Thank you for the further explanation, I do have one last question.
Should the data table look like what is below? This is the real data with many more codes and relationships. Should 80 be represented in the 'Hierarchy" calculation between 1st Hierarchy Codes 27-60?
I ask because when I start calculating total values I don't get the correct answer on certain relationships.
Please see the complete database below. Would you be able to recreate the formula and PowerBI example from this data set?
Thank you again for all of the help over the past few days!
Codes Value Hierarchy 1 Hierarchy 2 Hierarchy 3 Hierarchy 4 Hierarchy 5 Hierarchy 6 0.344 1 0.428 2 0.446 6 2.654 8 9 10 11 12 1.027 9 10 11 12 0.675 10 11 12 0.309 11 12 0.153 12 0.307 17 18 19 0.307 18 19 0.106 19 0.554 21 0.262 22 0.212 23 0.913 27 28 29 80 0.381 28 29 0.153 29 0.243 33 0.308 34 0.315 35 0.431 39 0.426 40 1.394 46 48 0.683 47 0.214 48 0.368 54 55 56 0.368 55 56 0.368 56 0.606 57 58 59 60 0.546 58 59 60 0.353 59 60 0.353 60 1.338 70 71 72 103 104 169 1.121 71 72 104 169 0.519 72 169 1.026 73 0.354 74 0.491 75 0.533 76 0.441 77 0.686 78 0.277 79 0.575 80 1.051 82 83 84 0.404 83 84 0.314 84 0.31 85 0.22 86 87 88 0.219 87 88 0.143 88 0.271 96 0.276 99 100 0.276 100 0.498 103 104 0.368 104 1.537 106 107 108 161 189 0.401 107 108 0.305 108 0.509 110 111 112 0.335 111 112 0.216 112 0.612 114 115 0.164 115 0.232 122 0.522 124 0.474 134 135 136 137 138 0.474 135 136 137 138 0.068 137 138 0.068 138 2.112 157 158 161 1.153 158 161 0.551 161 0.262 162 0.575 166 80 167 0.143 167 0.508 169 0.406 170 0.249 173 0.609 176 0.855 186 0.581 188 0.567 189
