Forum Discussion

mokhan's avatar
mokhan
Frequent Visitor
5 years ago
Solved

Comparison of previous years average with current months average in the bar graph

Hello All,

I am a beginner with Power BI.

 

I have a column with date and another column with values. I would like to generate a bar graph beginning with previous years average bar follwed by current months average bars. Something like below.

 

How can I acheive this using Power BI. Any help would be appreciated!

 

Thankyou!

Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi mokhan ,

    Based on your description, you can create a calulated column like this to use it as X-axis:

    Axis = 
IF (
    YEAR ( 'Table'[Date] ) = YEAR ( TODAY () ),
    'Table'[Date].[Month],
    CONVERT ( YEAR ( 'Table'[Date] ), STRING )
)

    Then create these to measures, one of them calculates average about each year and each month for current year, another one calulates average about all years as a target:

    Avg = AVERAGE('Table'[Values])
Avg_allyears = 
CALCULATE ( AVERAGE ( 'Table'[Values] ), ALL ( 'Table' ) )

    Use a 'Line and stacked column chart ' to show it:

    Attached a sample file in the below, hopes to help you.

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi mokhan ,

    Based on your description, you can create a calulated column like this to use it as X-axis:

    Axis = 
IF (
    YEAR ( 'Table'[Date] ) = YEAR ( TODAY () ),
    'Table'[Date].[Month],
    CONVERT ( YEAR ( 'Table'[Date] ), STRING )
)

    Then create these to measures, one of them calculates average about each year and each month for current year, another one calulates average about all years as a target:

    Avg = AVERAGE('Table'[Values])
Avg_allyears = 
CALCULATE ( AVERAGE ( 'Table'[Values] ), ALL ( 'Table' ) )

    Use a 'Line and stacked column chart ' to show it:

    Attached a sample file in the below, hopes to help you.

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • ToddChitt's avatar
    ToddChitt
    Icon for Super User rankSuper User
    5 years ago

    mokhan You asked this in the Power Query forum. This is probably better asked in the Desktop forum. But that won't stop people from trying to help you with your DAX

     

    If I understand correctly, you would first need to get the MONTHLY SUM of your Metric, and from there, get the AVERAGE of those sums for the LAST YEAR. Is that correct?

     

    Start with two calculated columns in your table: YEAR(Date) and MONTH(Date):

    TheYear = YEAR([Date]) and TheMonth = MONTH([Date])

    Then add a derived table in DAX:

    Monthly Sums = SUMMARIZE ( <fact table name>, [TheYear], [TheMonth], "Sum of Metric", SUM( [Some column] ) )

    Refer to this for SUMMARIZE: SUMMARIZE function (DAX) - DAX | Microsoft Docs

     

    Finally, create a MEASURE as:

    Average Monthly = AVG ( 'Montly Sums' [Sum of Metric] )

     

    Hope this helps