Forum Discussion

mokhan's avatar
mokhan
Frequent Visitor
5 years ago
Solved

Report

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!

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    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.

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

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

    • mokhan's avatar
      mokhan
      Frequent Visitor
      5 years ago

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

       

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    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.