How to calculate YTD % based on the filter/formula?

[romilv1]

Hi Guys,

I have one issue with YTD calculations. 

Formula for YTDActuals is; 

IF(CONTAINSSTRING(SELECTEDVALUE(vw_AdaptivePlanningActuals[accountName]),"%"),FORMAT(TOTALYTD(SUM(vw_AdaptivePlanningActuals[Amount.1]),'dim date'[Date].[Date]),"0.00"),
FORMAT(TOTALYTD(SUM(vw_AdaptivePlanningActuals[Amount.1]),'dim date'[Date].[Date]),"#,##"))

The formula says to format the columns that contains % as 0.00 else keep the format as #,##. This is how it looks (see below)

The only issue is that the % values are also adding up (see yellow). I need to filter the YTD column that should say if the account (first column) contains % then the values should be a formula. Example for Gross Profit % (last row) the value will be Total Revenue (1st row) divided by Team Gross Profit.(2nd last row) hence the value will NOT be 464 but rather 17,763,341/41,949,535 i.e. 42.34%

How do I fit this logic in my exisitng logic? IIf it is not possible to fit in existing formula, I can also create new measure then use that measure in the formula as below:

IF(CONTAINSSTRING(SELECTEDVALUE(vw_AdaptivePlanningActuals[accountName]),"%"),FORMAT(newmeasure,"0.00"),
FORMAT(TOTALYTD(SUM(vw_AdaptivePlanningActuals[Amount.1]),'dim date'[Date].[Date]),"#,##"))

What will be the logic of new measure? How to filter YTD using specific columns?

[v-cgao-msft]

Hi @romilv1 ,

Please try this measure:

YTDActuals =
IF (
    CONTAINSSTRING (
        SELECTEDVALUE ( vw_AdaptivePlanningActuals[accountName] ),
        "%"
    ),
    FORMAT (
        VAR __cur_ytd =
            TOTALYTD (
                SUM ( vw_AdaptivePlanningActuals[Amount.1] ),
                'dim date'[Date].[Date]
            )
        VAR __total =
            TOTALYTD (
                SUM ( vw_AdaptivePlanningActuals[Amount.1] ),
                'dim date'[Date].[Date],
                FILTER (
                    ALLSELECTED ( vw_AdaptivePlanningActuals ),
                    vw_AdaptivePlanningActuals[accountName] = "Total Revenue"
                )
            )
        RETURN
            DIVIDE ( __cur_ytd, __total ),
        "0.00"
    ),
    FORMAT (
        TOTALYTD (
            SUM ( vw_AdaptivePlanningActuals[Amount.1] ),
            'dim date'[Date].[Date]
        ),
        "#,##"
    )
)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[romilv1]

Hi @v-cgao-msft  

This works like wonder but the performance hits 3x when using the same DAX across multiple queries. 

How can the DAX be optimize so that the performance is not affected.

[romilv1]

Hi @v-cgao-msft  This partiallly works but the issue (which I forgot to mention) that "Total Revnue" is summation of 3 line items - Revenue A, B &C. 

How can I sum the revenue in the formula? Or Do I need to create another measure that bring the summation of 3 line items. Also the line item that says 

 SELECTEDVALUE ( vw_AdaptivePlanningActuals[accountName] ),
        "%"

Instead of % it should be Gross Profit % and  Project Gross Profit % as there are 2 different formula. How do I fit all of this logic in one formula?