Line and Stacked Chart, Line Y Axis is based on a calculated column and is not displaying correctly

[Han_Solo]

Hi Community

I am relativeley new to Power BI so please bear with me. The basics of my model are: Development program with costs and strart and finish dates of when they are incurred. I have also created a custom measure for the cumulative (running total) of costs based on their finish dates. My code is as follows: 

Cumulative Total 2 =

    VAR CurrentDate = MAX('Staggered Stage 1 (Lots 3,4,5)'[Date Finish])

    RETURN

    CALCULATE(

        SUM('Staggered Stage 1 (Lots 3,4,5)'[Cost  (inc. GST)]),

        FILTER(

            ALL('Staggered Stage 1 (Lots 3,4,5)'[Date Finish]),

            ('Staggered Stage 1 (Lots 3,4,5)'[Date Finish]) <= CurrentDate

        )

    )

I have a Line and Stacked Chart which works perfectly and shows item cost against time as bars and the cumulative total as a line as per the following:

The Problem: When I introduce another measure, being the item to the "Item" to the x-axis, the Y-line displays incorrectly. It is not longer the running total of the item costs over time. It's basically just reading the Sum of Cost at a particular date, without calclating the running total from previous items in the timeline as per the below: 

Any ideas would be appreciated and if you need any more info please let me know. 

Han. 

[Han_Solo]

@tharunkumarRTK thanks for the reply. As mentioned, I have another chart which does not include the second x-axis measure which works perfectly. However, I need the second measure so the end user can see when the cost items are incurred. Basically, I need the second x-axis items to show as an x-axis lable only, but the y-line to reference the first x-axis only (being the Date). 

[tharunkumarRTK · Accepted solution]

@Han_Solo 

I understand your requirement. I did not ask you to remove the 'Item' column from x-axis, I asked you to remove the filters on 'Item' selection in your cumulative sum measure, like below:

Cumulative Total 2 =
    VAR CurrentDate = MAX('Staggered Stage 1 (Lots 3,4,5)'[Date Finish])
    RETURN
    CALCULATE(
        SUM('Staggered Stage 1 (Lots 3,4,5)'[Cost  (inc. GST)]),
        FILTER(
            ALL('Staggered Stage 1 (Lots 3,4,5)'[Date Finish],'Staggered Stage 1 (Lots 3,4,5)'[Item]),
            ('Staggered Stage 1 (Lots 3,4,5)'[Date Finish]) <= CurrentDate
        )
    )

P.S. I wrote the above measure, assuming your date column and Item column are from same tables, you need to modify the formula further if that is not the case.

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[Han_Solo]

@tharunkumarRTK Thank you very much, that seems to have solved it! So, for my understanding, is the extra FILTER of 'Staggered Stage 1 (Lots 3,4,5)'[Item]), allowing the Line-y values to read from the Date Finish column? If so, what is the original filter of  'Staggered Stage 1 (Lots 3,4,5)'[Date Finish] doing?

[tharunkumarRTK]

@Han_Solo 

I think problem is with your x axis, you added a item into your x axis. It is filtering your value and it is not cumilating the value based on the Date Finish. You should remove the filter on. that column in your measure. 

Please share the PBIX file with sample data and expected result for further assistance. 

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!