Report

[mokhan]

I have Montly data bars on my bar graph and I would like to display bar with current year average and a custome value bar of target at the end, how can I achieve this.

Thankyou!

[v-alq-msft · Accepted solution]

Hi, @mokhan 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Table 2(a calculated table):

Table 2 = 
UNION(
    DISTINCT('Table'[YearMonth]),
    ROW("YearMonth","2020-Avg"),
    ROW("YearMonth","2021-Avg"),
    ROW("YearMonth","Target")
)

You may two measures as below.

Result = 
SWITCH(
    MAX('Table 2'[YearMonth]),
    "2020-Avg",
    CALCULATE(
        AVERAGE('Table'[Value]),
        FILTER(
            ALL('Table'),
            YEAR([Date])=2020
        )
    ),
    "2021-Avg",
    CALCULATE(
        AVERAGE('Table'[Value]),
        FILTER(
            ALL('Table'),
            YEAR([Date])=2021
        )
    ),
    "Target",12,
    CALCULATE(
        SUM('Table'[Value]),
        FILTER(
            ALL('Table'),
            [YearMonth]=MAX('Table 2'[YearMonth])
        )
    )
)

Color Control = 
SWITCH(
    MAX('Table 2'[YearMonth]),
    "2020-Avg","yellow",
    "2021-Avg","red",
    "Target","green"
)

Then you may use 'YearMonth' from 'Table 2' as 'Shared axis' and apply conditional format based on 'Color Control'.

Result:

Best Regards

Allan

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

[mokhan]

I need this

So far I got this

[mokhan]

Need to add current year avg and target bar at the end.

[AllisonKennedy]

@mokhan  You will need to do a lot of manual work/calculations to achieve this. Would you settle for using the Line Column combo chart and putting the custom target value as a line value? 

Or you could even use the Analytics features to set custom target as constant line and average for current year: 

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-analytics-pane

[amitchandak]

@mokhan ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.