Forum Discussion

Almantas32's avatar
Almantas32
Helper II
6 years ago
Solved

Monthly Goals gets aggregated

Hey, guys, 

 

I am trying to create a graph comparing actual monthly revenue, to the planned goals. 

The Revenue is a calculate measure - Total ex revenue wich is the sum of the Expected revenue column. 

 

And the Goals added myself and it looks like this:


I conected them via calendar Month of year column:

 

When I compare Total Ex revenue and the Goals in a table it's all good:


But when I do it in a graph, Power Bi aggregates the goal column, even though I have chosen the option Don't summarise as default:

 

 

My question is one of two: 

 

1. How do I combine these two measures in one graph? 

or

2. Should I go another way with goals to acctuals comparison? 

 

Keep it as simple as possible folks 🙂 

  • Hi Almantas32 ,

     

    We can try to create a measure to show the goal of each month in charts:

     

    Monthly Goal Measure =
    CALCULATE (
        SUM ( 'Goals Table'[Monthly Goals] ),
        FILTER (
            ALLSELECTED ( 'Goals Table' ),
            [Month Number]
                IN FILTERS ( 'Calendar Ex Closing'[MonthOfYear] )
        )
    )


    If it doesn't meet your requirement, Could you please show the relationship between the 'Goals Table' and the table which contain column used in the x-axis Field?


    Best regards,

     

3 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support

    Hi Almantas32 ,

     

    We can try to create a measure to show the goal of each month in charts:

     

    Monthly Goal Measure =
    CALCULATE (
        SUM ( 'Goals Table'[Monthly Goals] ),
        FILTER (
            ALLSELECTED ( 'Goals Table' ),
            [Month Number]
                IN FILTERS ( 'Calendar Ex Closing'[MonthOfYear] )
        )
    )


    If it doesn't meet your requirement, Could you please show the relationship between the 'Goals Table' and the table which contain column used in the x-axis Field?


    Best regards,

     

  • v-lid-msft's avatar
    v-lid-msft
    Community Support

    Hi Almantas32 ,

     

    How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


    Best regards,

  • Mariusz's avatar
    Mariusz
    Community Champion

    Hi Almantas32 

     

    Most probably its the fact that Year filter is not propagating your budget table.

    Use the pattern in the article: link below - to set your budget table correctly.

    https://www.daxpatterns.com/budget-patterns/ 

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn