Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Bar chart with 2 values

Hello,

 

I would like to put 2 values in a grouped bar chart. The values are "revenue selected year" and "revenue previous year", grouped in several categories (e.g.: region A, region B, region C).

 

So output should be a grouped bar chart like this:

- region A revenue selected year

- region A revenue previous year

- region B revenue selected year

- region V revenue previous year

- ...

 

Is this possible?

 

Regards

Dennis

Need Help
Tips and Tricks

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Wou can have region as axis and This year vs last year as values/measures using time intelligence and date table 

     

    example 

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
    Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
    This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
    Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
    Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
    Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))


    //Only year vs Year, not a level below

    This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
    Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
    rolling = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]>=max('Date'[Year])-2 && 'Date'[Year]<=max('Date'[Year])) )

    diff = [This Year]-[Last Year ]
    diff % = divide([This Year]-[Last Year ],[Last Year ])

     

    Power BI — Year on Year with or Without Time Intelligence
    https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hello amitchandak ,

     

    I do not get how this solves my problem. I do have regions as my axis, but i can just put one measure into values. So it either shows selected year or it shows previous year, what i want to do is but both values in the same bar chart.

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , can you share current setup? if you have both legend and axis they you can have only one measure

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi  Anonymous ,

    According to your description, I create this data：

    Here are the steps you can follow：

    1. Create calculated column.

    Year = YEAR('Table'[date])

    2. Place region to axis, year to legend and revenue to value

    3. Result：

    You can downloaded PBIX file from here.

     

    Best Regards,

    Liu Yang

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