How to get sum of YTD value not TYD of SUMS

[v_ichharam]

Hi. I have financial data from various companies. For company A data is provided as YTD values, for company B data is provided as monthly values. This can be seeen in the value column. I wrote the following to get values from both companies on the same standard i.e. YTD format.

Value_YTD = IF(

MAX(IS_consolidated[Company]) in {"Company B"},

TOTALYTD(

SUM(IS_consolidated[Value]),

'Date'[Date]),SUM(IS_consolidated[Value])

)

I then want to drill up the Value_YTD for company A and company B. When I do, the "drilled up" value is a YTD of the sums. This is not what I want. What I want is a sum of the YTD. Can someone help please? I have also illustrated the problem and desired result in the picture following.

[AlexisOlson · Accepted solution]

You can likely fix this by iterating over the companies so that the logic for each is handled separately. This might look something like this:

SUMX (
    VALUES ( IS_consolidated[Company] ),
    IF (
        IS_consolidated[Company] = "Company B",
        TOTALYTD ( SUM ( IS_consolidated[Value] ), 'Date'[Date] ),
        CALCULATE ( SUM ( IS_consolidated[Value] ) )
    )
)

However, the proper way to do this is to pre-process your data to standardize the data format rather than trying to deal with multiple formats simultaneously. You'll be glad you did in the long run.

[AlexisOlson · Accepted solution]

You can likely fix this by iterating over the companies so that the logic for each is handled separately. This might look something like this:

SUMX (
    VALUES ( IS_consolidated[Company] ),
    IF (
        IS_consolidated[Company] = "Company B",
        TOTALYTD ( SUM ( IS_consolidated[Value] ), 'Date'[Date] ),
        CALCULATE ( SUM ( IS_consolidated[Value] ) )
    )
)

However, the proper way to do this is to pre-process your data to standardize the data format rather than trying to deal with multiple formats simultaneously. You'll be glad you did in the long run.

[v_ichharam]

Thank you very much @AlexisOlson . I get your point on preprocessing the data as good practice.  A follow up question if I may?  Using your formula the above issue is resolved. However, I apply your formula and then take the output and standardise the signage by applying the following ruleset

Value_YTD_std =

SUMX(

VALUES ( 'Hierarchy'[Level_0] ),

IF (

'Hierarchy'[Level_0] in {"Revenue","Other Income"},

CALCULATE(ABS('Measures Table'[Test_Value_YTD])),

if('Hierarchy'[Level_0] in {"COS", "Marketing", "Opex", "Staff Costs"},

-1*'Measures Table'[Test_Value_YTD],

'Measures Table'[Test_Value_YTD]

)))

I get the following:

For some reason I get absolute value of A + B. What I am looking for is sum of C+D. Can you help?

Thank you

[AlexisOlson · Accepted solution]

The answer is the same. You'll need to iterate over the granularity you want to sum over.

Value_YTD_std_X = SUMX ( VALUES ( Company[CompanyName] ), [Value_YTD_std] )