Add YTD to matrix visual for Power BI desktop report

[Anonymous]

I have a matrix in a power bi desktop report in which I would like to add the YTD and baseline goals for different metrics(some are percentage values, others are float). In the rows field, I have the metrics, in the columns I have actuals and targets for different quarters(Q1 Actuals, Q1 targets), and in the values field, I have the values for the metrics.

After I created a YTD measure and added it to the values field in the matrix, it shows the YTD calculations for all the quarter actuals/targets(Q1 Actuals and Q2 Actuals). I want the YTD calculation to show as the last column in the matrix and not for each quarter. I would also like to add a baseline goal(it will be a measure) as the first column in the matrix.

[v-jialongy-msft]

 Hi @Anonymous 

If you simply use the YTD function as follows, it will be displayed at the end of each quarter.

If you use the following DAX, it will only be displayed at the end

YTD Measure without quarter = IF(

    ISINSCOPE('Table'[date].[Quarter]),

    BLANK(),

    TOTALYTD(SUM('Table'[value]), 'Table'[date])

)

You also want to add a baseline goal as the first column in the matrix

Since you have not provided your specific data, I can only provide you with DAX that may implement your needs

Baseline Goal Measure = CALCULATE(SUM('YourTable'[YourGoalColumn]), 'YourTable'[MetricType] = "Baseline")

If you need further help, please provide your sample data

^{Best Regards,}

^Jayleny

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[Anonymous]

Hi @v-jialongy-msft , thanks for your help. I tried this and it's nearly working. In my matrix table, the YTD measure keeps showing up for every quarter but I would only like this to show up as the last column in the matrix and the subtotal for the YTD in the last column is over 100%, which needs to be corrected

Here's an example of my matrix:
As you can see below, the YTD measure keeps showing up very every quarter in the columns which I need to get rid of. Also, the subtotal for the YTD in the last column is over 100%, which needs to be corrected. 
I would appreciate any help, thanks.

[v-jialongy-msft · Accepted solution]

 Hi @Anonymous 

In response to your question, I have created the following table

For the issue you mentioned that YTD is more than 100%, I updated my formula

YTDD = IF(

    ISINSCOPE('Table'[date].[Quarter]),

    BLANK(),

    TOTALYTD(DIVIDE(SUM('Table'[Actual Goal]),SUM('Table'[Target Goal]),BLANK()), 'Table'[date])

)

For issues where you only want to show YTD in the last column, this is a by design issue for power bi.

^{Best Regards,}

^Jayleny

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[audreygerred]

Hi! Sounds like you have your subtotals on for columns. If you don't want the YTD amount to show after each quarter, try adjusting the subtotals showing from the editing pane for that visual.

[Anonymous]

Hi @audreygerred, thanks for your help. I tried this and it's not working because the column subtotals are counting the columns with the quarter targets, in addition to the quarter actuals.