Forum Discussion

memon_rehan's avatar
memon_rehan
Copper Contributor
Apr 01, 2019

AVERAGES Calculation Error in PowerPivot for Heirarchical Data

Hello Friends,

I used the DAX Parent Child Hierarchy DAX Model from this link https://www.daxpatterns.com/parent-child-hierarchies/ to build my own Organization GOALS Heirarchical Data Model

 

In the measure formula as explained in the above link, I replaced the SUM with AVERAGE

 

Original Formula as per the above link

[Sales Amount Simple] :=
IF (
    [BrowseDepth] > [MaxNodeDepth],
    BLANK (),
    SUM ( Transactions[Amount] )
)

 

Changed Formula

[GoalsAchieved] :=
IF (
    [BrowseDepth] > [MaxNodeDepth],
    BLANK (),
    AVERAGE( ConsolidatedGoals[Actual] )
)
 
However the result is not exactly as per the required average.
 
Can someone help me with a solution
 
BI & Data Analytics
excel

3 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 01, 2019

    Hi memon_rehan ,

     

    It shows correct result. If you go back to source table and calculate it here

    you see the same results as in PivotTable. That's since 3.6.4 has subitems and

    AVERAGE(AVERAGE(range1)+AVERAGE(range2))  or
(range1/n1 + range2/n2)/2

is not equal to

AVERAGE(range1, range2)  or
(range1, range2)/(n1+n2)

    Above is also in attached file

    • memon_rehan's avatar
      memon_rehan
      Copper Contributor
      Apr 14, 2019

      SergeiBaklan 

      You are right but what i want is Average of only Levels below it and not everything below it.

       

    • memon_rehan's avatar
      memon_rehan
      Copper Contributor
      Apr 02, 2019

      SergeiBaklan Thanks for the quick reply. It might the correct for Microsoft Excel calculation, however I need the right percentage based on the selection. Is there anything i can do with the Measure formula to achieve this ?

Resources