line sum of line total doesn't match group total

[Anonymous]

Hi Guys

I need help with this strange behavior. I actually tried it also in excel and got same strange results, therefore i think it is more my not understanding basic algerbra than wrong calculation.

i have a table like this

country	ytd	ly ytd	ly total	ytd target	total target
Austria	2,973.00	2,175.00	14,760.00	2,918.00	19,800.00
Germany	23,883.00	29,383.00	123,977.00	31,758.00	134,000.00
Switzerland	4,533.00	2,940.00	8,988.00	3,598.00	11,000.00
Total sum	31,389.00	34,498.00	147,725.00	38,274.00	164,800.00
Total calculated	31,389.00	34,498.00	147,725.00	38,485.00	164,800.00

where we have sales data ytd , ly ytd , ly total, ytd target and total target

the ytd target is calculated like ly ytd/ly total x total target. 

as you can see in the columns E5 and E6 i should get the same number. 

the E5 is calculated as sum of E2-4 and the E6 is calculated like C6/D6xF6

Is there a way how i can get to the same number. ?

hope you can help 

thank you 

m

[v-shex-msft · Accepted solution]

HI @Anonymous ,

This is a common measure total issue, when measure calculated on total level, it direct calculate with summarize total row contents instead of drill to bottom level and summary each row result.

You can try to use following formula to add a condition to check current level and write formula works with total level:

_ytd target =
IF (
    ISFILTERED ( Table4[country] ),
    SUM ( [ly ytd] ) / SUM ( [ly total] )
        * SUM ( [total target] ),
    SUMX (
        SUMMARIZE (
            Table4,
            [country],
            [ly ytd],
            Table4[ly total],
            Table4[total target],
            "YTD target", [ly ytd] / [ly total] * [total target]
        ),
        [YTD target]
    )
)

Regards,

Xiaoxin Sheng