Home

AVERAGES Calculation Error in PowerPivot for Heirarchical Data

%3CLINGO-SUB%20id%3D%22lingo-sub-391612%22%20slang%3D%22en-US%22%3EAVERAGES%20Calculation%20Error%20in%20PowerPivot%20for%20Heirarchical%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-391612%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20644px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F100770i784B5A1BCFC4C7B1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Collapsed%203.0.JPG%22%20title%3D%22Collapsed%203.0.JPG%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20649px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F100771iCAA62A5AB8E7287C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Expanded%203.0.JPG%22%20title%3D%22Expanded%203.0.JPG%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20651px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F100772i43491260E1D95463%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Expanded%203.6%20with%20subtotal.JPG%22%20title%3D%22Expanded%203.6%20with%20subtotal.JPG%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20650px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F100773i4E0FDAD5DCE0F327%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Expanded%203.6.JPG%22%20title%3D%22Expanded%203.6.JPG%22%20%2F%3E%3C%2FSPAN%3EHello%20Friends%2C%3C%2FP%3E%3CP%3EI%20used%20the%20DAX%20Parent%20Child%20Hierarchy%20DAX%20Model%20from%20this%20link%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.daxpatterns.com%2Fparent-child-hierarchies%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.daxpatterns.com%2Fparent-child-hierarchies%2F%3C%2FA%3E%20to%20build%20my%20own%20Organization%20GOALS%20Heirarchical%20Data%20Model%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20measure%20formula%20as%20explained%20in%20the%20above%20link%2C%20I%20replaced%20the%20SUM%20with%20AVERAGE%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOriginal%20Formula%20as%20per%20the%20above%20link%3C%2FP%3E%3CDIV%20class%3D%22line%20number1%20index0%20alt2%22%3E%5BSales%20Amount%20Simple%5D%20%3A%3D%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number2%20index1%20alt1%22%3EIF%20(%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number3%20index2%20alt2%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%5BBrowseDepth%5D%20%26gt%3B%20%5BMaxNodeDepth%5D%2C%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number4%20index3%20alt1%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BBLANK%20()%2C%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number5%20index4%20alt2%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BSUM%20(%20Transactions%5BAmount%5D%20)%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number6%20index5%20alt1%22%3E)%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EChanged%20Formula%3C%2FP%3E%3CDIV%20class%3D%22line%20number1%20index0%20alt2%22%3E%5BGoalsAchieved%5D%20%3A%3D%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number2%20index1%20alt1%22%3EIF%20(%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number3%20index2%20alt2%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%5BBrowseDepth%5D%20%26gt%3B%20%5BMaxNodeDepth%5D%2C%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number4%20index3%20alt1%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BBLANK%20()%2C%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number5%20index4%20alt2%22%3E%26nbsp%3B%20%26nbsp%3B%20%3CSTRONG%3EAVERAGE(%20ConsolidatedGoals%5BActual%5D%20)%3C%2FSTRONG%3E%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number6%20index5%20alt1%22%3E)%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number6%20index5%20alt1%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number6%20index5%20alt1%22%3EHowever%20the%20result%20is%20not%20exactly%20as%20per%20the%20required%20average.%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number6%20index5%20alt1%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number6%20index5%20alt1%22%3ECan%20someone%20help%20me%20with%20a%20solution%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number6%20index5%20alt1%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-391612%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-392753%22%20slang%3D%22en-US%22%3ERe%3A%20AVERAGES%20Calculation%20Error%20in%20PowerPivot%20for%20Heirarchical%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-392753%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThanks%20for%20the%20quick%20reply.%20It%20might%20the%20correct%20for%20Microsoft%20Excel%20calculation%2C%20however%20I%20need%20the%20right%20percentage%20based%20on%20the%20selection.%20Is%20there%20anything%20i%20can%20do%20with%20the%20Measure%20formula%20to%20achieve%20this%20%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-391981%22%20slang%3D%22en-US%22%3ERe%3A%20AVERAGES%20Calculation%20Error%20in%20PowerPivot%20for%20Heirarchical%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-391981%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F258002%22%20target%3D%22_blank%22%3E%40memon_rehan%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20shows%20correct%20result.%20If%20you%20go%20back%20to%20source%20table%20and%20calculate%20it%20here%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20354px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F100812iD4950CD69205090A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eyou%20see%20the%20same%20results%20as%20in%20PivotTable.%20That's%20since%203.6.4%20has%20subitems%20and%3C%2FP%3E%0A%3CPRE%3EAVERAGE(AVERAGE(range1)%2BAVERAGE(range2))%20%20or%0A(range1%2Fn1%20%2B%20range2%2Fn2)%2F2%0A%0Ais%20not%20equal%20to%0A%0AAVERAGE(range1%2C%20range2)%20%20or%0A(range1%2C%20range2)%2F(n1%2Bn2)%3C%2FPRE%3E%0A%3CP%3EAbove%20is%20also%20in%20attached%20file%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-450940%22%20slang%3D%22en-US%22%3ERe%3A%20AVERAGES%20Calculation%20Error%20in%20PowerPivot%20for%20Heirarchical%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-450940%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20are%20right%20but%20what%20i%20want%20is%20Average%20of%20only%20Levels%20below%20it%20and%20not%20everything%20below%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F108674i56C708C426AA152F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Level3Example.JPG%22%20title%3D%22Level3Example.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
memon_rehan
New Contributor

Collapsed 3.0.JPGExpanded 3.0.JPGExpanded 3.6 with subtotal.JPGExpanded 3.6.JPGHello 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
 
3 Replies

Hi @memon_rehan ,

 

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

image.png

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

@Sergei Baklan 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 ?

@Sergei Baklan 

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

 

Level3Example.JPG

Related Conversations